problem with join

[omid020]

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 beacause in assign_details table those fields are not NULL . How can I solve this issue ?

 

[r937]

No , I don`t use mysql manual ,

shame on you, it is a marvelous resource and will answer many of your questions (a lot faster than waiting for answers on a forum, too)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[omid020]

Sorry , I think there is a misunderstanding ! Excuse me if I made you upset , I have a mysql manual but it`s not easy to find and fluence for me , I`ve learned many basic conceptions from SQL lessons in

http://www.w3schools.com

and mysql manual but sometimes when my problems get complicated I try to find similar examples in web and tutorials to solve my problem faster . I`m a beginner so that`s clear that I can`t solve any issues with reading manuals and tutorials . I think learning programming is very hard for me because I can`t find many concepts and answers easily from books or main manuals but when I use somewhere like this forum , your amazing comments tell me what I should learn in next step .

Again thanks for your help and advice.

Regards
Omid

 

[r937]

don't worry, you did not make me upset

however, if you had checked the manual, the very first sentence on the page for UNION is as follows:

UNION is used to combine the result from [red]many[/red] SELECT statements into one result set.

"many" usually means more than two, so you would not have needed to ask me







r937.com | rudy.ca
Buy my new book Simply SQL from Amazon