I can`t retrive all required data from both tables

[omid020]

Hello ,
There is 4 tables in my DB :

assign_details
receipt_details
rs_leaves
rs_colors

I should get all available data from "assign_details" and "receipt_details" tables , but now my query shows only common available data . For example when "assign_details" has an element that there is not in "receipt_details" or vice versa , query doesn`t show related row data to me . I tried to use Left Join or Righ Join but when I use it for one of them only data related to parent table will be shown and same data in other table doesn`t load in query , this is related SQL :

Select assign_details.leafCode,
  rs_leaves.leafName,
  rs_colors.colorsCode,
  COALESCE(Sum(Distinct receipt_details.recAmount), 0),
  COALESCE(Sum(Distinct assign_details.assAmount), 0),
  COALESCE(Sum(Distinct receipt_details.recAmount), 0) - COALESCE(Sum(Distinct
  assign_details.assAmount), 0) As 'FWarehouse'
From assign_details Inner Join
  rs_leaves On assign_details.leafName = rs_leaves.leafID Inner Join
  rs_colors On assign_details.leafColor = rs_colors.colorsID Inner Join
  receipt_details On rs_leaves.leafID = receipt_details.leafName And
    rs_colors.colorsID = receipt_details.leafColor
Group By assign_details.leafCode,
  rs_leaves.leafName,
  rs_colors.colorsCode
Order By rs_leaves.leafCode

Also this screenshot shows tables details :

How can I reach to my favorite result ?

 

[omid020]

Because I want to migrate this SQL code to MySQL , I can`t use (Full Join) .
This is an example to describe more about this DB content and related problem :
rs_leaves table has data such :
leafID---> 1
leafCode---> A
leafName---> Wood

rs_colors table has data such :
colorsID---> 1
colorsCode---> 6028
--------------------
colorsID---> 2
colorsCode---> 8015

Now let`s assum that (assign_details) table has this item:
leafName---> Wood
leafColor---> 6028
assAmount---> 25

and (receipt_details) table has this item:
leafName---> Wood
leafColor---> 8015
recAmount---> 48

Now when I run below query , both items (i.e (Wood 6028) and (Wood 8015)) doesn`t load in result , when I use Left(Right) Join to one of (assign_details) or (receipt_details) table , just one of related data will be shown (Wood 6028 or Wood 8015) .
When I use Left(Right) Join for both tables no data will be shown !

Is my database structure correct ?

 

[omid020]

Sry , I forgot to write this query :

COALESCE(Sum(Distinct receipt_details.recAmount), 0) - COALESCE(Sum(Distinct assign_details.assAmount), 0) As 'FWarehouse'

 

[gmmastros]

Did you try changing all the joins to left join or did you only change one of them? I think you probably need to change them all.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[omid020]

4 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 as my desire 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 or colorsCode . How can I solve this issue ?

 

[gmmastros]

You can replace nulls with empty strings by using the coalesce function, like this:

Select [!]Coalesce([/!]rs_leaves.leafCode, [!]'') As leafCode,[/!]
  [!]Coalesce([/!]rs_colors.colorsCode, [!]'') As 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

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[omid020]

Thanks gmmastros , my problem is solved here :

http://www.tek-tips.com/viewthread.cfm?qid=1584918