MySQL Join Help

[sknagesh]

Hi

 

I Have two tables

 

 

CREATE TABLE InProcess (

InProcess_ID int(10) unsigned NOT NULL,

Operation_ID int(10) unsigned NOT NULL,

Basic_Dimn varchar(15) NOT NULL,

PRIMARY KEY (InProcess_ID)

);

 

CREATE TABLE InprocessDimns (

InProcessDimn_ID int(10) unsigned NOT NULL,

IP_ID int(10) unsigned NOT NULL,

Operation_ID int(10) unsigned NOT NULL,

Batch_ID int(10) unsigned NOT NULL,

Job_NO varchar(20) NOT NULL,

Dimn_Measured varchar(20) NOT NULL,

PRIMARY KEY (InProcessDimn_ID)

);

 

Sample Data for InProcess Table

 

InProcess_ID Operation_ID Basic_Dimn

 

1  10    25.5

2  10    32.0

3  10    1

4  10    2.5

5  10    5.0

6    10    5.0

7    10    22.0

8    10    13.1

9    10    12.2

10  10    2.0

 

Sample Data for InprocessDimns

 

InProcessDimn_ID  IP_ID Operation_ID  Batch_ID  Job_NO  Dimn_Measured

1  5, 10, 11, 35, 5.02

2  7,  10,  11, 35, 21.98

3  10, 10, 11, 35, 2.0

 

As shown above Table InProcess has some 10 rows of data for Operation_ID='10' and Table InprocessDimns has 3 rows of data linked to Operation_ID of 10. What I want to do is show all data of Operation_ID(10) along with corresponding data from InprocessDimns.

 

 

 

 

Here is the SQL I wrote.

 

SELECT ip.* FROM InProcess AS ip LEFT OUTER JOIN InprocessDimns AS ipd ON ip.InProcess_ID=ipd.IP_ID WHERE ip.Operation_Id='10' AND Job_NO='35'.

 

But this is giving me only 3 rows of data where InProcess_ID and IP_ID match. But I want all 10 rows from InProcess along with data from 3 rows in InprocessDimns.

 

Any help is appreciated.

 

Regards

 

SKN

[kickstart]

Hi

 

Not sure why you are checking for 35.

 

This:-

 

SELECT InProcess_ID, Operation_ID, Basic_Dimn, InProcessDimn_ID, IP_ID, Operation_ID, Batch_ID, Job_NO, Dimn_Measured
FROM InProcess
LEFT OUTER JOIN InprocessDimns
ON InProcess.InProcess_ID = InprocessDimns.IP_ID
WHERE InProcess.Operation_Id = 10

 

will get all the rows, with blank columns where there is no matching record.

 

With checking the job_no, you want to only do the join on columns which have 35 in that field, rather than doing an outer join then excluding records without 35 in that field:-

 

SELECT InProcess_ID, Operation_ID, Basic_Dimn, InProcessDimn_ID, IP_ID, Operation_ID, Batch_ID, Job_NO, Dimn_Measured
FROM InProcess
LEFT OUTER JOIN InprocessDimns'
ON InProcess.InProcess_ID = InprocessDimns.IP_ID AND InprocessDimns.Job_NO=35
WHERE InProcess.Operation_Id = 10

 

All the best

 

Keith

[sknagesh]

Thanks

 

That worked

 

SKN