Search code examples
phpmysqlsqljoinwhere-in

PHP/MySQL Using multiple WHEREs in one SELECT query

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?

Solution

  • 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.