problem with join

omid020 · Jan 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 :

Code:

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 · Jan 1, 2010

how can you solve the issue? don't mix LEFT and RIGHT joins

if you want any further help, you will unfortunately need to describe what you're trying to do and what the two details tables are for

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

omid020 · Jan 1, 2010

Thanks Rudy for your reply ,
let`s assume there is a (warehouse), surely every warehouse
has some inputs and outputs . To calculate warehouse inventory
we use this formula : (inputs - outputs)
In my database inputs are in receipt_details table and outputs
are in assign_details table ; our inventory is leaf of some
special material (like plastic) . In database different type of
leaves (our inventory types) are placed in rs_leaves table . also any leaf could have
many colors that available colors are placed in rs_colors table ,
so inventory is a leaf that at least has one color (colored leaf) .
There is no rule for content of inputs and outputs , for example
a colored leaf can have output value without having input value and
vice versa .Now in first step I should be able to load all available
data in receipt_details and sent_details tables with related leafCode
and leafColor .
This is my current database status :

r937 · Jan 1, 2010

Now in first step I should be able to load all availabledata in receipt_details and sent_details tables with related leafCodeand leafColor

you'll want a UNION query, not a FULL OUTER JOIN

one SELECT to pull the inputs, and another SELECT to pull the outputs, and then UNION the results

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

omid020 · Jan 1, 2010

Maybe I`m wrong , but from last post I found that I could write this query :

Code:

SELECT rs_leaves.leafCode,
  rs_colors.colorsCode,
  SUM(Distinct receipt_details.recAmount) AS INPUT,
  SUM(Distinct assign_details.assAmount) AS OUTPUT,
SUM(Distinct receipt_details.recAmount) - SUM(Distinct assign_details.assAmount) AS INVENTORY
FROM assign_details, receipt_details
LEFT JOIN rs_leaves
ON receipt_details.leafName = rs_leaves.leafID
LEFT JOIN rs_colors
ON receipt_details.leafColor = rs_colors.colorsID
GROUP BY rs_leaves.leafCode, rs_colors.colorsCode
UNION
SELECT rs_leaves.leafCode,
  rs_colors.colorsCode,
  SUM(Distinct receipt_details.recAmount) AS INPUT,
  SUM(Distinct assign_details.assAmount) AS OUTPUT,
SUM(Distinct receipt_details.recAmount) - SUM(Distinct assign_details.assAmount) AS INVENTORY
FROM receipt_details, assign_details
LEFT JOIN rs_leaves
ON assign_details.leafName = rs_leaves.leafID
LEFT JOIN rs_colors
ON assign_details.leafColor = rs_colors.colorsID
GROUP BY rs_leaves.leafCode, rs_colors.colorsCode;

But the result is not what I expect

----------+------------+-------+--------+-----------+
| leafCode | colorsCode | INPUT | OUTPUT | INVENTORY |
+----------+------------+-------+--------+-----------+
| A | 1014 | 200 | 85 | 115 |
| A | 1032 | 30 | 85 | -55 |
| B | 2000 | 112 | 85 | 27 |
| E | 1032 | 34 | 85 | -51 |
| I | 2000 | 36 | 85 | -49 |
| I | 6028 | 8 | 85 | -77 |
| A | 1014 | 420 | 24 | 396 |
| A | 1020 | 420 | 30 | 390 |
| B | 2000 | 420 | 8 | 412 |
| E | 1032 | 420 | 12 | 408 |
| I | 1014 | 420 | 20 | 400 |
| I | 2000 | 420 | 6 | 414 |
| N | 1020 | 420 | 5 | 415 |
+----------+------------+-------+--------+-----------+

For example final result should tell me "A" "1014" has "200" input and "24" output , so INVENTORY value should be "200 - 24" equal "176" but now I can`t have this result with using UNION join .
I`m confused , please help me .

r937 · Jan 1, 2010

But the result is not what I expect

that's right

okay, i'll help you, let's take it in steps

step #1 -- write a query to get all inputs, ~no~ outputs

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

omid020 · Jan 1, 2010

Thanks

This is first step :

Code:

SELECT rs_leaves.leafCode,
  rs_colors.colorsCode,
  SUM(Distinct receipt_details.recAmount) AS INPUT
FROM receipt_details
LEFT JOIN rs_leaves
ON receipt_details.leafName = rs_leaves.leafID
LEFT JOIN rs_colors
ON receipt_details.leafColor = rs_colors.colorsID
GROUP BY rs_leaves.leafCode, rs_colors.colorsCode;

r937 · Jan 1, 2010

okay, now we are getting somewhere

let me ask you, is it ever possible for a row in the receipt_details table to have a value in its leafName column that ~doesn't exist~ in ~any~ leafID column of the rs_leaves table?

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

omid020 · Jan 1, 2010

No , it`s not possible . Every value in receipt_details.leafName exits in rs_leaves.leafID .
receipt_details table content confirm it :

+-----------+-------+----------+----------+-----------+-----------+------------+
| 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 |
+-----------+-------+----------+----------+-----------+-----------+------------+

r937 · Jan 1, 2010

in that case, your query should use INNER JOINs instead of LEFT OUTER JOINs

can you do the second step now?

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

omid020 · Jan 1, 2010

Mmmm , I like but now I`m going to bed , can I do it tomorrow ?
If you have any trouble with next time , I`ll keep it on tonight . What`s your idea ?

r937 · Jan 1, 2010

i've been here for many years, i'll still be here tomorrow

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

omid020 · Jan 2, 2010

Hi Rudy ,

What should I do for second step ?

r937 · Jan 2, 2010

first step was to write a SELECT that returns all receipt_detail data with INNER JOINs to leaves and colours

second step is to write a second SELECT that returns all assign_detail data with INNER JOINs to leaves and colours

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

omid020 · Jan 2, 2010

Ok ,
I wrote this :

Code:

SELECT rs_leaves.leafCode,
  rs_colors.colorsCode,
  SUM(Distinct assign_details.assAmount) AS OUTPUT
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
GROUP BY rs_leaves.leafCode, rs_colors.colorsCode;

and result is :

+----------+------------+--------+
| leafCode | colorsCode | OUTPUT |
+----------+------------+--------+
| A | 1014 | 24 |
| A | 1020 | 30 |
| B | 2000 | 8 |
| E | 1032 | 12 |
| I | 1014 | 20 |
| I | 2000 | 6 |
| N | 1020 | 5 |
+----------+------------+--------+

r937 · Jan 2, 2010

okay, now let's combine your two queries...

Code:

SELECT leafCode
     , colorsCode
     , SUM(`INPUT`) AS `INPUT`
     , SUM(`OUTPUT`) AS `OUTPUT`
  FROM ( [red]SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , SUM(receipt_details.recAmount) AS `INPUT`
              , NULL                           AS `OUTPUT`
           FROM receipt_details
         INNER
           JOIN rs_leaves
             ON rs_leaves.leafID = receipt_details.leafName
         INNER
           JOIN rs_colors
             ON rs_colors.colorsID = receipt_details.leafColo
         GROUP 
             BY rs_leaves.leafCode
              , rs_colors.colorsCode[/red]
         UNION ALL
         [blue]SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , NULL
              , SUM(assign_details.assAmount) AS OUTPUT
           FROM assign_details
         INNER 
           JOIN rs_leaves
             ON rs_leaves.leafID = assign_details.leafName 
         INNER 
           JOIN rs_colors
             ON rs_colors.colorsID = assign_details.leafColor
         GROUP 
             BY rs_leaves.leafCode
              , rs_colors.colorsCode[/blue]
       ) AS humpty
GROUP
    BY leafCode
     , colorsCode

please test it like this, then go and put back the DISTINCT inside the SUMs which i had removed, and compare the results

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

omid020 · Jan 2, 2010

Wonderful result :->

This is code :

Code:

SELECT leafCode
     , colorsCode
     , COALESCE(SUM(`INPUT`), 0) AS `INPUT`
     , COALESCE(SUM(`OUTPUT`), 0) AS `OUTPUT`
     , COALESCE(SUM(`INPUT`), 0) - COALESCE(SUM(`OUTPUT`), 0) AS `INVENTORY`
  FROM ( SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , SUM(receipt_details.recAmount) AS `INPUT`
              , NULL AS `OUTPUT`
           FROM receipt_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = receipt_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = receipt_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
         UNION ALL
         SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , NULL AS `INPUT`
              , SUM(assign_details.assAmount) AS `OUTPUT`
           FROM assign_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = assign_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = assign_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
       ) AS humpty
GROUP
    BY leafCode
     , colorsCode

And this is the result :
+----------+------------+-------+--------+-----------+
| leafCode | colorsCode | INPUT | OUTPUT | INVENTORY |
+----------+------------+-------+--------+-----------+
| A | 1014 | 200 | 24 | 176 |
| A | 1020 | 0 | 30 | -30 |
| A | 1032 | 30 | 0 | 30 |
| B | 2000 | 112 | 8 | 104 |
| E | 1032 | 34 | 12 | 22 |
| I | 1014 | 0 | 20 | -20 |
| I | 2000 | 36 | 6 | 30 |
| I | 6028 | 8 | 0 | 8 |
| N | 1020 | 0 | 5 | -5 |
+----------+------------+-------+--------+-----------+

Also I used Distinct in sum statement but there is no difference in result !

Can I use UNION ALL for more that 2 SELECT queries ?

omid020 · Jan 2, 2010

Sorry for my mistake [censored]

Can I use UNION ALL for more than 2 SELECT queries ?
Is UNION for joining two SELECT statements only !?

r937 · Jan 2, 2010

what mistake? you mean the DISTINCT mistake? ;-)

yes, you can UNION multiple SELECTs

you do have a copy of the mysql manual, right?

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

omid020 · Jan 2, 2010

No , I don`t use mysql manual , I`ve just tried to find something from simple googling [wink]

Thanks for your peerless support , although my database will grow more but I hope to solve other future problems myself [hourglass]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

problem with join

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor