Join Query solution for multiple table check

Databases
1

hi,
I have six tables each having a column field (order_no), now i want to match that something like this.

1- select order_no from table 1 where order_no != with order_no present in all other six tables.

Can any one suggest which join query or which query i should use to achieve this.
thanks

2

use LEFT OUTER JOINs instead of INNER JOINs in the query i gave you previously, and then add an IS NULL check for each table’s join column

3

Hi again thanks for quick reply,
I make this query but giving only single result instead of multiple should come,
Please check the query code

Select estimat_customer.order_no from estimat_customer 
  LEFT
  JOIN prepress
  ON prepress.order_no =estimat_customer.order_no IS NULL
LEFT
  JOIN press
    ON press.order_no = estimat_customer.order_no IS NULL
LEFT
  JOIN postpress
    ON postpress.order_no = estimat_customer.order_no IS NULL
LEFT
  JOIN qc
    ON qc.order_no = estimat_customer.order_no IS NULL
LEFT
  JOIN binding
    ON binding.order_no = estimat_customer.order_no IS NULL
LEFT
  JOIN dispatch 
    ON dispatch.order_no = estimat_customer.order_no IS NULL
 WHERE estimat_customer.order_no ='$order_num' order by order_no DESC";

what i want to achieve is
Table 1 have Order_no 2,4,6,7,8,9,5,1
And all 6 tables have order_no 4 in there column
so the result should be
Table 1- 2,6,7,8,9,5,1

thanks

4

Use this to but no working.

Select estimat_customer.order_no from estimat_customer 
  LEFT OUTER
  JOIN prepress
  ON prepress.order_no =estimat_customer.order_no  
LEFT OUTER
  JOIN press
    ON press.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN postpress
    ON postpress.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN qc
    ON qc.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN binding
    ON binding.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN dispatch 
    ON dispatch.order_no = estimat_customer.order_no
 WHERE estimat_customer.order_no ='$order_num' order by estimat_customer.order_no DESC";
5

you forgot the IS NULL checks

6 
Select estimat_customer.order_no from estimat_customer 
  LEFT OUTER
  JOIN prepress
  ON prepress.order_no =estimat_customer.order_no  
LEFT OUTER
  JOIN press
    ON press.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN postpress
    ON postpress.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN qc
    ON qc.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN binding
    ON binding.order_no = estimat_customer.order_no
LEFT OUTER
  JOIN dispatch 
    ON dispatch.order_no = estimat_customer.order_no
 WHERE estimat_customer.order_no ='$order_num' IS NULL order by estimat_customer.order_no DESC";

no values coming.

7 
Select estimat_customer.order_no from estimat_customer 
  LEFT OUTER
  JOIN prepress
  ON prepress.order_no =estimat_customer.order_no IS NULL  
LEFT OUTER
  JOIN press
    ON press.order_no = estimat_customer.order_no IS NULL
LEFT OUTER
  JOIN postpress
    ON postpress.order_no = estimat_customer.order_no IS NULL
LEFT OUTER
  JOIN qc
    ON qc.order_no = estimat_customer.order_no IS NULL
LEFT OUTER
  JOIN binding
    ON binding.order_no = estimat_customer.order_no IS NULL
LEFT OUTER
  JOIN dispatch 
    ON dispatch.order_no = estimat_customer.order_no IS NULL
 WHERE estimat_customer.order_no ='$order_num' order by estimat_customer.order_no DESC";

Same only one value coming.

8

no, that’s invalid syntax, man

did you not read the article i linked to?

the IS NULL checks go in the WHERE clause

p.s. also, the ORDER BY is not wrong, but since you’re retrieving only one order_no, there’s not much point in sorting on it

9

hi,
I use that too but giving only one value instead of multiple according to my current database setting.
please see post before this. (1st of above reply).
thanks

10

that has incorrect syntax too

WHERE estimat_customer.order_no ='$order_num' IS NULL

please, go back to that article i linked to and try to understand it

here’s the example –


SELECT o.*
  FROM outer o
LEFT 
  JOIN inner i
    ON i.value = o.value
 WHERE i.value IS NULL
11

According the above code i create this but still no luck,

Select estimat_customer.order_no from estimat_customer 
  LEFT 
  JOIN prepress
  ON prepress.order_no =estimat_customer.order_no where prepress.order_no IS NULL
LEFT
  JOIN press
    ON press.order_no = estimat_customer.order_no where press.order_no IS NULL
LEFT
  JOIN postpress
    ON postpress.order_no = estimat_customer.order_no where postpress.order_no IS NULL
LEFT
  JOIN qc
    ON qc.order_no = estimat_customer.order_no where qc.order_no IS NULL
LEFT
  JOIN binding
    ON binding.order_no = estimat_customer.order_no where binding.order_no IS NULL
LEFT
  JOIN dispatch 
    ON dispatch.order_no = estimat_customer.order_no where dispatch.order_no IS NULL
 WHERE estimat_customer.order_no ='$order_num' order by estimat_customer.order_no DESC";

Thanks

12

please, please, PLEASE stop attaching IS NULL to the join conditions

the IS NULL tests go into the WHERE clause

there can be only one WHERE clause

did you not see the example in post #10?

do you not understand how outer joins work?

13

Hi,
Please check #6 in which i put is NULL on the where clause, but you said that is also the wrong syntax,
then what is the right syntax?

What i think you are saying to do something like this

Select estimat_customer.order_no from estimat_customer 
  LEFT 
  JOIN prepress
  ON prepress.order_no =estimat_customer.order_no
LEFT
  JOIN press
    ON press.order_no = estimat_customer.order_no
LEFT
  JOIN postpress
    ON postpress.order_no = estimat_customer.order_no
LEFT
  JOIN qc
    ON qc.order_no = estimat_customer.order_no
LEFT
  JOIN binding
    ON binding.order_no = estimat_customer.order_no
LEFT
  JOIN dispatch 
    ON dispatch.order_no = estimat_customer.order_no
 WHERE estimat_customer.order_no IS NULL order by estimat_customer.order_no DESC";

But also wouldn’t help because there must be a order no to match all queries that’s why i am putting " =‘$order_num’ " to check.

1- Yes i know there can be only one where clause but you saying every of my syntax is wrong that’s why i put those extra where clause.
2- Yes, i see the example but that is only for 2 tables and i have 6 that’s why the confusion is coming and rest i have one variable or a value to match that not present in example.
3- May be no, I haven’t use any of join query, i check several example and read theory but theory is my weak point and i usually do not under stand by that.

Sorry for inconvenience but i am not that good in MYSQL, and i haven’t use these queries in practical.

thanks

14

i would like you to please run the following query twice –

SELECT estimat_customer.order_no 
     , prepress.order_no
  FROM estimat_customer 
LEFT OUTER 
  JOIN prepress
    ON prepress.order_no = estimat_customer.order_no
 WHERE estimat_customer.order_no = '$order_num' 
   AND prepress.order_no IS NULL

run it once with the last line, and once without

examine the output in both cases, and then tell me what it’s doing

pick an $order_num that that exists in both tables, and the do it all again with an $order_num that exists only in the left table

15

1- Case 1, it doesn’t showing any value.
2- Case 2, it showing same order no which i am giving.

thanks

16

hi,
This the case
Table estimate
order no
1,2,3,4,5,6
Table prepress
order no
1,2,3,4,5,6
Table Press
order no
1,2,3,4,5
table Postpress
order no
1,2,3,4
table qc
order no
1,2,3
table binding
order no
1,2
table dispatch
order no
1
Here only order no 1 present in all table, so the result should return
Order no
2,3,4,5,6 as per estimate table
thanks

17

Hi,
Solved.
i did some hit & trial then get this to full fill, what i am looking for.


Select estimat_customer.order_no from estimat_customer where estimat_customer.order_no NOT IN
 (select prepress.order_no from prepress 
join press ON press.order_no=prepress.order_no
join postpress ON postpress.order_no=prepress.order_no 
join qc ON qc.order_no=prepress.order_no 
join binding ON binding.order_no=prepress.order_no 
join dispatch ON dispatch.order_no=prepress.order_no)

thanks