SELECT * FROM leads as l, accounts AS a WHERE
l.leads_company like '%office%' or
l.leads_lastname like '%office%' and
l.account_id = a.account_id
ORDER BY l.leads_company


ok I have this query BUT it comes back with over 531 records, but it should only come back with 5 or so records.

I am still grasping the SQL queries...

Thanks
John

what I want to do is do a search within many different fields, so the user doesn't have to select the field in which he/she wants to search within.

as you see above I want to add more fields but in this query it will make the number of records grow out of control. I need to see if a serachterm is in each one of the fields.

John

SELECT * FROM leads as l, accounts AS a WHERE
l.leads_lastname like '%office%' or
l.leads_firstname like '%office%' or
l.leads_company like '%office%' or
l.leads_city like '%office%' or
l.leads_spouse like '%office%' or
l.leads_comments like '%office%' and
l.account_id = a.account_id
ORDER BY l.leads_company

/me thinks you need some brackets

what kind of brackets?

Is this run against a MySQL DB? I'm not familiar with MySQL so I don't know if you have available to you the INNER JOIN construct -- would make it look a lot nicer to look at.

But assuming you don't have INNER JOIN available to you, then what sasha said is right... you need some parentheses.

Here's what I suspect you're trying to achieve:

SELECT * FROM leads AS l, accounts AS a
WHERE (l.leads_lastname like '%office%'
OR l.leads_firstname like '%office%'
OR l.leads_company like '%office%'
OR l.leads_city like '%office%'
OR l.leads_spouse like '%office%'
OR l.leads_comments like '%office%')
AND l.account_id = a.account_id
ORDER BY l.leads_company

Notice the parantheses around just the columns you're trying to search.

Your original SQL was matching up the matching record in the 'leads' table with every single row of data that was in the 'accounts' table.

SELECT * FROM leads as l, accounts AS a WHERE
(l.leads_lastname like '%office%' or
l.leads_firstname like '%office%' or
l.leads_company like '%office%' or
l.leads_city like '%office%' or
l.leads_spouse like '%office%' or
l.leads_comments like '%office%') and
l.account_id = a.account_id
ORDER BY l.leads_company



you DA man

You can have inner joins, I can write recursive functions, but I can't figure out joins in SQL...

Man

( <---- that kind

sorry that was rude, but it wont let me delete it

lol it's ok. I thought it was funny. Ask a dumb question get a dumb response.

Where is the "any key" button?