Page 1 of 1

Compare two values

Posted: Fri Jul 10, 2009 2:36 pm
by melomonk
Hello

I'm stuck on a query I'm putting together and I was hoping a fresh pair of eyes could help me see my mistake....

I have database with three tables = tour, user & brokerage
I'm to return the results of: All user tours for two different brokerage companies

This statement Works and returns the data for company one....how do i get results for company one& company two???

Working query

Code: Select all

$sql = "SELECT * FROM tour LEFT JOIN user ON tour.userid=user.userid LEFT JOIN brokerage ON user.brokerageid = brokerage.brokerageid  WHERE brokerage.brokerage = 'Company One' GROUP BY tour.userid ORDER BY brokerage.brokerageid ";
Attempted query

Code: Select all

$sql = "SELECT * FROM tour LEFT JOIN user ON tour.userid=user.userid LEFT JOIN brokerage ON user.brokerageid = brokerage.brokerageid  WHERE brokerage.brokerage = 'Company One' AND brokerage.brokerage = 'Company Two' GROUP BY tour.userid ORDER BY brokerage.brokerageid ";
This attempt returns nothing.......

Re: Compare two values

Posted: Fri Jul 10, 2009 3:37 pm
by andyhoneycutt
that's because brokerage.brokerage is never going to be two different values.

instead try where brokerage.brokerage IN ('Company One','Company Two')

Or you could replace your "AND brokerage.brokerage =" with an "OR brokerage.brokerage ="

-Andy

Re: Compare two values

Posted: Fri Jul 10, 2009 3:59 pm
by pickle
I'm not sure you need all that fancy LEFT JOIN stuff. Try this:

Code: Select all

SELECT
  tour.*
FROM
  tour,
  user,
  brokerage
WHERE
  tour.userid = user.userid AND
  user.brokerageid = brokerage.brokerageid
ORDER BY
  brokerage.brokerageid

Re: Compare two values

Posted: Fri Jul 10, 2009 4:05 pm
by andyhoneycutt
Now that's some sexy sql. 8)