PHP/MySQL Using multiple WHEREs in one SELECT query

Question

I have 2 tables.

Table A: trades: which contains the columns: tradeID, tradeName, tradeShow, and tradeGuy.

Table B: offers: which contains the columns: tradeID, offerName, offerGuy.

I'm trying to select all columns from table A (trades) WHERE the value of "tradeShow" = 'Yes', And the value of "tradeGuy" != the user's Username. That much is easy, but I also don't want to select any records which have an offer created by the user. In other words, in table B (offers), offerGuy != Username WHERE trade ID from Table B = tradeID from Table A.

But, how do I merge these 2 conditions? I've tried this:

$sql = "SELECT *
FROM trades t1
JOIN offers t2 
ON (t1.tradeID = t2.tradeID)
WHERE t1.tradeShow='Yes' AND t1.tradeGuy!='$username' AND t2.offeringGuy!='$username'";

But the problem with that is it only selects the records from trades which have an offer, because of the forth line: ON (t1.tradeID = t2.tradeID), as in it only selects trades which have a record in (offers) that mentions their tradeID.

I've also tried an awkward attempt to link the 2 tables with a meaningless link by adding a "linker" column to each table with the default value of "XXX", and did this:

$sql = "SELECT *
FROM trades t1
JOIN offers t2 
ON (t1.linkerA = t2.linkerB)
WHERE t1.tradeShow='Yes' AND t1.tradeGuy!='$username' AND (t2.offeringGuy!='$username' WHERE t1.tradeID=t2.tradeID)";

But the problem with that is using 2 Where clauses... So, how do I merge the 2 conditions?

score 2 · Accepted Answer · answered Jul 23 '14 at 13:48

What you're looking for is called an OUTER JOIN (in this case a LEFT OUTER JOIN) which will give you null results for missing matches, something like;

SELECT *
FROM trades t1
LEFT OUTER JOIN offers t2 
  ON t1.tradeID = t2.tradeID AND t2.offeringGuy = '$username'
WHERE t1.tradeShow='Yes' AND t1.tradeGuy!='$username' AND t2.offeringGuy IS NULL

We add a condition to the LEFT JOIN that we're only interested in matches against t2.offeringGuy = '$username', which will return NULL values in t2's fields if there is no match.

Then we just check that t2.offeringGuy IS NULL to find the non matches.

Thanks alot! I've been staring at this for ages, trying to think of an answer, and jumping from one question to another. Thanks again! — MNOPYZ, Jul 23 '14 at 14:11

score 1 · Answer 2 · answered Jul 23 '14 at 13:52

I would do this with not exists rather than an explicit join:

SELECT *
FROM trades t 
WHERE t.tradeShow = 'Yes' AND t.tradeGuy <> '$username' and
      not exists (select 1
                  from offers o
                  where t.tradeID = o.tradeID and o.tradeGuy = '$username'
                 );

PHP/MySQL Using multiple WHEREs in one SELECT query

2 Answers2