I discovered one more thing about this whole thing that does not make any sense to me but I have been told I have to take into account.
Here is what I have Email Table emailAddr (field) [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Domain Table domain(field) acme.com test.com another.com The count is not as important at creating a list of all users in each domain and users with a -xxx number at the end are considered different than users without the -xxx number. So essentially here is what I am needed.... Domain email addresses acme.com [EMAIL PROTECTED] acme.com [EMAIL PROTECTED] acme.com [EMAIL PROTECTED] test.com [EMAIL PROTECTED] test.com [EMAIL PROTECTED] test.com [EMAIL PROTECTED] another.com [EMAIL PROTECTED] another.com [EMAIL PROTECTED] another.com [EMAIL PROTECTED] Thanks in advance, -brandon [EMAIL PROTECTED] 01/28/2004 01:09 PM To David Griffiths <[EMAIL PROTECTED]> cc [EMAIL PROTECTED] Subject Re: comparing one field to another. 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 > >
