Compare two values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
melomonk
Forum Newbie
Posts: 10
Joined: Thu Jul 09, 2009 10:28 pm

Compare two values

Post 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.......
Last edited by pickle on Fri Jul 10, 2009 3:57 pm, edited 1 time in total.
Reason: Fixing [code] tags
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Compare two values

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Compare two values

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Compare two values

Post by andyhoneycutt »

Now that's some sexy sql. 8)
Post Reply