CODEWITHSUNDEEP
mysqlsqlleft-join
pnescior
pnescior

Reputation: 136

LEFT JOIN - fetching all data from left table with no matches in the right one

In my project, I have two tables like this: 

parameters (
    id PRIMARY KEY,
    name
)

and

parameters_offeritems (
    id_offeritem,
    id_parameter,
    value,
    PRIMARY KEY (id_offeritem, id_parameter)
)

I'm not showing structure of offeritems table, because it's not necessary.

Some sample data:

INSERT INTO parameters (id, name) VALUES
    (1, 'first parameter'), (2, 'second parameter'), (3, 'third parameter')

INSERT INTO parameters_offeritems (id_offeritem, id_parameter, value) VALUES
    (123, 1, 'something'), (123, 2, 'something else'), (321, 2, 'anything')

Now my question is - how to fetch (for given offer ID) list of all existing parameters, and moreover, if for the given offer ID there are some parameters set, I want to fetch their value in one query.

So far, I made query like this:

SELECT p.*, p_o.value FROM parameters p LEFT JOIN parameters_offeritems p_o
ON p.id = p_o.id_parameter WHERE id_offeritem = OFFER_ID OR id_offeritem IS NULL

But it fetches only those parameters, for which there are no existing records in parameters_offeritems table, or parameters, for which value are set only for the current offer.

Upvotes: 0

Views: 2502

Answers (3)

Alex
Alex

Reputation: 17289

Your query was almost perfect, just WHERE in wrong pace:

SELECT p.*, p_o.value FROM parameters p 
LEFT JOIN ( 
     SELECT * FROM parameters_offeritems 
     WHERE id_offeritem = OFFER_ID) as p_o
ON p.id = p_o.id_parameter

Upvotes: 0

Tom
Tom

Reputation: 6663

To get all parameters, plus the value of any parameters set for a specific Offer Item, you need to move the Offer ID logic into the join like this (see below). 

SELECT  p.*, p_o.value 

FROM    parameters p 

        LEFT JOIN parameters_offeritems p_o
        ON p.id = p_o.id_parameter 
        AND id_offeritem = OFFER_ID;

If you have logic in your WHERE clause referring to fields in a table you are doing a LEFT JOIN on, you effectively change your JOIN to an INNER JOIN (unless you are checking for a NULL).

Upvotes: 1

Charlie Martin
Charlie Martin

Reputation: 112346

The magic word you're looking for is OUTER JOIN. Jeff Atwood did a nice Venn-diagram explanation here.

Upvotes: 0

Related Questions