omid020 Posted January 1, 2010 Share Posted January 1, 2010 Hi, 4 required tables content is described below : 1- rs_leaves +--------+----------+-----------------------------------------+ | leafID | leafCode | leafName | +--------+----------+-----------------------------------------+ | 1 | A | WOOD 1 | | 2 | B | WOOD 2 | | 3 | C | WOOD 3 | | 4 | D | WOOD 4 | | 5 | E | WOOD 5 | | 6 | F | Antik 1 | | 7 | G | Antik 2 | | 8 | H | Antik 3 | | 9 | I | Antik 4 | | 10 | J | Antik 5 | | 11 | K | Stone 1 | | 12 | L | Stone 2 | | 13 | M | Stone 3 | | 14 | N | Stone 4 | | 15 | O | Stone 5 | | 16 | P | Stone 6 | +--------+----------+-----------------------------------------+ 2- rs_colors +----------+------------+---------------+ | colorsID | colorsCode | colorsPicture | +----------+------------+---------------+ | 1 | 1014 | 1014.png | | 2 | 1032 | 1032.png | | 3 | 2000 | 2000.png | | 4 | 2001 | 2001.png | | 5 | 1020 | 1020.png | | 6 | 6028 | 6028.png | +----------+------------+---------------+ 3- receipt_details +-----------+-------+----------+----------+-----------+-----------+------------+ | detailsID | recID | leafName | leafCode | leafColor | recAmount | recComment | +-----------+-------+----------+----------+-----------+-----------+------------+ | 1 | 1 | 1 | A | 1 | 200 | NULL | | 2 | 1 | 2 | B | 3 | 112 | NULL | | 3 | 1 | 1 | A | 2 | 30 | NULL | | 4 | 2 | 9 | I | 3 | 36 | NULL | | 5 | 2 | 5 | E | 2 | 34 | NULL | | 6 | 6 | 9 | I | 6 | 8 | NULL | +-----------+-------+----------+----------+-----------+-----------+------------+ 4- assign_details +-----------+-------+-----------+----------+----------+-----------+-----------+-----------------------+ | detailsID | assID | projectID | leafName | leafCode | leafColor | assAmount | assComment | +-----------+-------+-----------+----------+----------+-----------+-----------+-----------------------+ | 1 | 1 | 1 | 1 | A | 1 | 20 | NULL | | 2 | 1 | 1 | 2 | B | 3 | 8 | NULL | | 3 | 2 | 2 | 1 | A | 1 | 4 | NULL | | 4 | 2 | 2 | 9 | I | 3 | 6 | NULL | | 5 | 2 | 1 | 1 | A | 5 | 30 | NULL | | 6 | 3 | 2 | 5 | E | 2 | 12 | NULL | | 7 | 3 | 1 | 14 | N | 5 | 5 | NULL | | 8 | 1 | 1 | 9 | I | 1 | 20 | NULL | +-----------+-------+-----------+----------+----------+-----------+-----------+-----------------------+ I wrote this query : Select rs_leaves.leafCode, rs_colors.colorsCode, receipt_details.recAmount, assign_details.assAmount From receipt_details Left Outer Join rs_leaves On receipt_details.leafName = rs_leaves.leafID Left Outer Join rs_colors On receipt_details.leafColor = rs_colors.colorsID Right Join assign_details On assign_details.leafName = rs_leaves.leafID And assign_details.leafColor = rs_colors.colorsID Query Result : +----------+------------+-----------+-----------+ | leafCode | colorsCode | recAmount | assAmount | +----------+------------+-----------+-----------+ | A | 1014 | 200 | 20 | | B | 2000 | 112 | 8 | | A | 1014 | 200 | 4 | | I | 2000 | 36 | 6 | | NULL | NULL | NULL | 30 | | E | 1032 | 34 | 12 | | NULL | NULL | NULL | 5 | | NULL | NULL | NULL | 20 | +----------+------------+-----------+-----------+ Now result shows all available data related to both recAmount and assAmount fields ,but for those rows that have NULL value for recAmount , leafCode and colorsCode are NULL too but I don`t like have NULL value for leafCode and colorsCode :-\ because in assign_details table those fields are not NULL . How can I solve this issue ? Link to comment https://forums.phpfreaks.com/topic/186845-problem-with-join-tables/ Share on other sites More sharing options...
fenway Posted January 3, 2010 Share Posted January 3, 2010 Huh? Link to comment https://forums.phpfreaks.com/topic/186845-problem-with-join-tables/#findComment-987754 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.