Hi,
I've the following table specs and the sample data for each of the tables:
Code:
CREATE TABLE tbl1 (
mem_id MEDIUMINT UNSIGNED NOT NULL,
cat CHAR(3) NOT NULL,
item_id SMALLINT UNSIGNED NOT NULL,
);
CREATE TABLE tbl2 (
cat CHAR(3) NOT NULL,
item_id MEDIUMINT UNSIGNED NOT NULL,
item TINYTEXT NOT NULL,
);
CREATE TABLE tbl3 (
mem_id MEDIUMINT UNSIGNED NOT NULL,
cat CHAR(3) NOT NULL,
item_id MEDIUMINT UNSIGNED NOT NULL,
selected TINYTEXT NOT NULL,
);
#tbl1
INSERT INTO tbl1 VALUES ('1', '201', '1');
INSERT INTO tbl1 VALUES ('1', '201', '3');
INSERT INTO tbl1 VALUES ('2', '201', '1');
INSERT INTO tbl1 VALUES ('2', '201', '2');
INSERT INTO tbl1 VALUES ('3', '201', '1');
#tbl2
INSERT INTO tbl2 VALUES ('201', '1', 'this is item1');
INSERT INTO tbl2 VALUES ('201', '2', 'this is item2');
INSERT INTO tbl2 VALUES ('201', '3', 'this is item3');
#tbl3
INSERT INTO tbl3 VALUES ('1', '201', '1', 'selected1');
INSERT INTO tbl3 VALUES ('2', '201', '2', 'selected2');
I then do the following query:
Code:
SELECT tbl1.mem_id, tbl1.cat, item, selected
FROM tbl1
LEFT JOIN tbl3 USING (mem_id, cat, item_id)
LEFT JOIN tbl2 USING (cat, item_id)
WHERE tbl1.mem_id=2
The result I got was:
Code:
mem_id cat item selected
2 201 NULL NULL
2 201 this is item2 selected2
I need the output to be:
Code:
mem_id cat item selected
2 201 this is item1 NULL
2 201 this is item2 selected2
Could someone enlighten me on how to accomplish that?
Thanks in advance
