Compare fields in query.

[alsjka]

I have the following data i would like to compare.

Lastname, first initial - catalognum

smithr
smitht
reissec - This one has two first initials that need to be compared instead on just one initial.

Against the following - emp.email

rsmith@tonnes-thine.com
tsmith@tonnes-thine.com
ecreiss@tonnes-thine.com


The following only works with the first initial. This only works so so I need to compare the catalognum again the emp.email i think i need to flip the catalognum's 1st or 2nd initials then compare that against the characters to the left of the @ sign in the emp.email.


SELECT DISTINCT PURREQ.CATALOGNUM,PURREQ.ITEMNUM, PURREQ.DESCRIPTIONONPO, PURREQ.PONUM,PURREQ.VENDORNAME, purreq.qtyrequested, purreq.dategenerated, purreq.total, po.PURCHASINGCENTERID,emp.email ,purreq.seqnum
FROM PURREQ,PO, EMP
WHERE PURREQ.PONUM = PO.PONUM and SUBSTRING(catalognum, 1, (LEN(catalognum) - 1)) = SUBSTRING(emp.email, 2, (LEN(emp.email) - 18))
and PO.STATUS = 'Open'
and PURREQ.QTYRECEIVED is null
and purreq.invytype != 'serv'
and purreq.siteid = emp.siteid
and (PURREQ.CATALOGNUM != '' and PURREQ.CATALOGNUM is not null and PURREQ.CATALOGNUM != 'none')
GROUP BY PURREQ.CATALOGNUM, PURREQ.ITEMNUM, PURREQ.DESCRIPTIONONPO, purreq.ponum,PURREQ.VENDORNAME,PURREQ.QTYRECEIVED,purreq.qtyrequested, purreq.dategenerated, purreq.total, po.PURCHASINGCENTERID,emp.email,purreq.seqnum
order by purreq.ponum


I hope I explained it. Any help is great! Thanks

 

[r937]

where does the coldfusion part come in?

and how in the world are we supposed to know if it's one initial character or two?

by the way, you don't need DISTINCT in this query

r937.com | rudy.ca

 

[alsjka]

This is just my query from my code everything else works but this. I am trying to compare smithr or smitht or reissec against rsmith@tonnes-thine.com or tsmith@tonnes-thine.com or
ecreiss@tonnes-thine.com. I did post this under Microsoft: Access Queries and JET SQL did not know where this would apply.

Thanks for the note on distinct..