CODEWITHSUNDEEP
mysqlsqlselect
Daehue Kim
Daehue Kim

Reputation: 64

mysql query where = value could exist or could not exist

Hello I have a problem regarding a MySQL query.

The following query is what I want to get a result.

However, one the thumb_file column in the color_all table could have data or be NULL.

    $query =  "SELECT DISTINCT(p.location), p.no, c.thumb_file FROM product_pics AS p, color_all AS c 
                WHERE style_number = '$style' AND p.color = c.color ORDER BY p.sort ASC";

Actually, I can make this query like the following 2 queries:

    $query = "SELECT DISTINCT(location), no, color FROM product_pics WHERE style_number = '$style' ORDER BY ASC";
    $result = mysql_query($query);
    while(list($loc, $no, $color) = mysql_fetch_array($result)){
          $sub_query = "SELECT thumb_file FROM color_all WHERE color = '$color'";
    }

But I just want to know how to make this 2 queries as one.

Upvotes: 0

Views: 42

Answers (1)

dmfay
dmfay

Reputation: 2477

Selecting FROM multiple tables performs a join of the two, and your WHERE clause is filtering out any resulting rows where the colors do not match, including where product_pics has no color information at all. This is equivalent to an INNER JOIN:

SELECT DISTINCT(p.location), p.no, c.thumb_file
FROM product_pics AS p
INNER JOIN color_all AS c ON c.color = p.color
WHERE style_number = '$style'
ORDER BY p.sort ASC";

Run that query, and you should see exactly the same result as you have right now: only rows where product_pics has a color and that color is matched in color_all are returned.

What you're looking for is an OUTER JOIN, which does not filter out rows where one side or the other has a null value in the join column. Outer joins come in two flavors, LEFT and RIGHT. The former is most common, since generally queries are written progressing from a source table to an auxiliary table. This query should give you what you want:

SELECT DISTINCT(p.location), p.no, c.thumb_file
FROM product_pics AS p
LEFT OUTER JOIN color_all AS c ON c.color = p.color
WHERE style_number = '$style'
ORDER BY p.sort ASC";

Upvotes: 1

Related Questions