That works great! My head can take a rest now and the bruises hopefully will heal.
Thanks, -brandon David Griffiths <[EMAIL PROTECTED]> 01/28/2004 12:05 PM To [EMAIL PROTECTED], [EMAIL PROTECTED] cc Subject Re: comparing one field to another. I'm not sure you can do that in a single select - one is a count, and the other is a list. In fact, finding the count requires a sub-select, or two seperate selects. Not sure what your columns are, so I will assume that emailAddress has a column called email, and domain has one called domain. SELECT count(*) FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)); This takes the length of domain and uses that value to grab those characters from the email address. So [EMAIL PROTECTED] becomes RIGHT([EMAIL PROTECTED], 9) which equals acme.com, which is the email-address domain, and thus you can do a compare. To select a list, just change the query to be, SELECT email FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)) AND domain=?; I am assuming you want to find all email addresses for a single domain of your choosing. Note that using RIGHT and LENGTH in the where clause will prevent the optimizer from using indexes. I don't have a database in front of me, so I can't check that the statements are perfect, but they should give you the idea. David. ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 28, 2004 6:31 AM Subject: comparing one field to another. > I have been beating my head against the wall for a couple of days now > and > cannot figure this out. I am hoping that it can be done. > > I have two tables each with only one field. > > Table 1 - emailAddress (this contains things like [EMAIL PROTECTED], > [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], > [EMAIL PROTECTED], etc) > Table 2 - domains (this contains acme.com, test.com, funny.com, etc) > > What I need is the ability to count the number of email address and list > > them for each domain. These tables are coming from two different > sources > so I cannot control what I recieve. I tried to use a LIKE but that does > > not seem to work. > > I am hoping this makes sense. > > Thanks, > > -brandon > >
