Querying data that matches in two different tables

[weswhite7]

The code is attached. Basically there are two tables, contact and recruiter.
When you initially add someone to contact, it also adds certain information to
the recruiter table along with the primary id just writen to the contact
table... confused yet? Basicaly, the attached code shows a search of the
database on form submission. I can't figure out how I get it to search the
first one and show only the ones where they are in the first db, and the second
db based on primary ids I'd assume.

Anybody have an idea?

<cfquery name="grabusers" username="sla" password="slaera" datasource="sla">
SELECT *
FROM contact
where <cfif form.firstname eq ""><cfelse>fname like
'%#form.firstname#%'</cfif><cfif form.lastname eq ""><cfelseif form.firstname
eq "">lname like '%#form.lastname#%'<cfelse>and lname like
'%#form.lastname#%'</cfif>
ORDER BY lname asc;
</cfquery>

[warddc]

select c.*, r.*
from contact c, recruiter r
where c.id=r.id

this will return all columns joined on id. you can change the select to
return only those columns you are interested in from each table.

d.

[weswhite7]

ok that makes sense, the problem is im searching based on a first and last name
input. so if i put in wes, it pulls up anyone with "wes" as their name. that
comes from the contact table. so then how do i take that output and match it to
only where that id is present in teh recruiter table?

[mxstu]

Originally posted by: weswhite7
ok that makes sense, the problem is im searching based on a first and last
name input. so if i put in wes, it pulls up anyone with "wes" as their name.
that comes from the contact table. so then how do i take that output and match
it to only where that id is present in teh recruiter table?

You need to add an extra condition to the where clause provided warddc:

--- psuedo-code
select (some columns)
from contact c, recruiter r
where c.id=r.id AND
c.fName = 'Wes'

Just don't use SELECT * , as in your original post. It requires more work by
the database and usually returns more information than you actually need, which
can affect performance. It can also cause problems with your CF output, when
joining two or more tables that share some of the same column names.