Compare Tables

[joshl_1995]

Hello Daniweb Community,
I've been looking around online for help with this but I can't seem to find a way to get a query to do what I want.
The image below should pretty much say what kind of result I'd like to get, it's pretty much merging the tables weather or not the second table has a row with the same ID and if it doesn't then just return NULL.

Thanks

[ryantroop]

If I am reading it right, it looks like you just want a left outer join on ItemID...

[AndrisP]

I think you need RIGHT JOIN something like this:

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`(
    `prod_id` INT PRIMARY KEY
    ,`prod_name` VARCHAR(30)
    ,`prod_descr` VARCHAR(90)
    ,`prod_price` DECIMAL(7,2)
    ,`prod_stock` INT
    ,`prod_date` DATE
);
DROP TABLE IF EXISTS `wish_list`;
CREATE TABLE `wish_list`(
    `wish_id` INT PRIMARY KEY
    ,`wish_account` INT
    ,`wish_item_id` INT
    ,`wish_date` DATE
);
INSERT INTO `products`(
    `prod_id`
    ,`prod_name`
    ,`prod_descr`
    ,`prod_price`
    ,`prod_stock`
    ,`prod_date`)
VALUES (16575
    ,'Product One'
    ,'This is the first product!'
    ,10,2,'2016-03-20')
    ,(573552
    ,'Product Two'
    ,'This is the second product!'
    ,5,20,'2016-03-20');
INSERT INTO `wish_list`(
    `wish_id`
    ,`wish_account`
    ,`wish_item_id`
    ,`wish_date`)
VALUES (25745735
    ,54246326
    ,16575
    ,'2016-02-29')
, (265734573
    ,54246326
    ,3143
    ,'2016-02-29');
CREATE OR REPLACE VIEW `exp_result` AS
    SELECT w.`wish_id`, w.`wish_account`
    , w.`wish_item_id`, w.`wish_date`
    , p.`prod_id`, p.`prod_name`
    , p.`prod_descr`, p.`prod_price`
    , p.`prod_stock`, p.`prod_date` 
FROM `products` p
    RIGHT JOIN `wish_list` w
    ON p.`prod_id` = w.`wish_item_id`;
SELECT * FROM `exp_result`;

[ryantroop]

:-/ I.. really don't know what to say here. The difference between a left join and a right join are... I guess a design choice...

however, I still think the answer above is a bit over complicated.. as seen here:

http://sqlfiddle.com/#!9/8ca784/2/0
(it's slow, I know, but let it load)

And to be quite honest, I feel this is as close to a homework query as you can get... so I didn't feel like answering it outright out of principle. Sorry if my assumption is incorrect, but this is pretty simple stuff for SQL :-/

[joshl_1995]

@ryantroop Thanks so much it works perfect!