Join Query

tsimsha · November 25th, 2004, 11:00 PM

Hello mates,

I have two tables. operationdetails and dailyoperations
Structure:
operationdetails:
-----------------
indexno (PK) job_code operation_no description

dailyoperations:
-----------------
dindex empid job_code operation_no qtyfinishes

I need a report to get

job_code(from table1) operation_no(from tab1) finishedqty(tab2)

Ex:
04A001 10 200
04A002 15 150
04A003 17 0 (means no records in tab2 for that operation)
.
.
.

The first table qry is like this
select job_code,operation_no from operationdetails where description='QUALITY CHECKING'

I need to get sum(qty) from second table whose job_code and operation_no matches.

Can u suggest the query?

ChrisScott · November 26th, 2004, 04:26 AM

Hi,

What you can get away with will depend on your database, but the following works with MySQL

Code:

SELECT od.job_code, od.operation_no, COUNT(do.dindex) 
FROM operationdetails od LEFT JOIN dailyoperations do 
ON od.job_code = do.job_code AND od.operation_no = do.operation_no 
GROUP BY od.job_code, od.operation_no;

HTH,

Chris

milk_vanilla · December 2nd, 2004, 09:31 AM

can i select 2 or more tables without join it? and is there any disadvantages or the advantages...without make the tables joined.may u give me diexamples query, without join mode. thx
cheers