How to get data check from multiple tables?

[rajeshkr]

Hi, 
I have multiple table 
Table -1 
order_no name 
1 raj 

table -2 
order_no name 
1 raj 

table 3 
order_no name 
1 raj 

table 4 
order_no name 
1 raj 

table 5 
order_no name 
1 raj 

I want a query to check if the id (one) is present in all tables or not, 
i create this. 


select order_no from prepress, press, postpress, qc, binding, dispatch where order_no=prepress.order_no AND order_no=press.order_no AND order_no=postpress.order_no AND order_no=qc.order_no AND order_no=binding.order_no AND order_no=dispatch.order_no 
but the ambiguous error for order_no. 

how to achieve this can anyone suggest me? 
thanks

[Barand]

Hi, 

I have multiple table 

 

 

Why?

[rajeshkr]

Why?

Hi,

To store different information to different tables but order_no will same for each table.

Thanks

[Barand]

But you are storing the same information to different table, just with different values. Table1, Table2 etc all have the same format.

 

Or am I being misled by a poor naming in the question and they are supposed to be the press, prepress, postpress, qc, binding and dispatch (six) tables in your query? Why not say so if that is the case?

 

Assuming that is the case the ambiguity is caused by your use of order_no in the WHERE clause without a qualifying table name. Don't use that syntax for table joins, use more efficient explicit joins

SELECT order_no 
FROM prepress
    INNER JOIN press ON press.order_no=prepress.order_no
    INNER JOIN postpress ON postpress.order_no=prepress.order_no
    INNER JOIN qc ON qc.order_no=prepress.order_no
    INNER JOIN binding ON binding.order_no=prepress.order_no
    INNER JOIN dispatch ON dispatch.order_no=prepress.order_no 

[rajeshkr]

 

But you are storing the same information to different table, just with different values. Table1, Table2 etc all have the same format.

 

Or am I being misled by a poor naming in the question and they are supposed to be the press, prepress, postpress, qc, binding and dispatch (six) tables in your query? Why not say so if that is the case?

 

Assuming that is the case the ambiguity is caused by your use of order_no in the WHERE clause without a qualifying table name. Don't use that syntax for table joins, use more efficient explicit joins

SELECT order_no 
FROM prepress
    INNER JOIN press ON press.order_no=prepress.order_no
    INNER JOIN postpress ON postpress.order_no=prepress.order_no
    INNER JOIN qc ON qc.order_no=prepress.order_no
    INNER JOIN binding ON binding.order_no=prepress.order_no
    INNER JOIN dispatch ON dispatch.order_no=prepress.order_no 

thanks that's because there are several other value will be in all six tables. but these two values will be same to check.

and i get the solution as you provide

SELECT prepress.order_no 
  FROM prepress
INNER
  JOIN press
    ON press.order_no = prepress.order_no 
INNER
  JOIN postpress
    ON postpress.order_no = prepress.order_no 
INNER
  JOIN qc
    ON qc.order_no = prepress.order_no 
INNER
  JOIN binding
    ON binding.order_no = prepress.order_no 
INNER
  JOIN dispatch 
    ON dispatch.order_no = prepress.order_no
 WHERE prepress.order_no ='$order_num'";

thanks