Unions or Joins with 3 tables? 1

[iiplusiii]

Any suggestions would be greatly appreciated. I need to have this in our next release by Monday. Thanks, Lisa

I am trying to accomplish a tricky merging of data. I have 4 tables - One master (TREQ), and 3 subtables (MOPCD, MOPCK, MOPBL). I currently have working a union statement that allows me to combine the information I need between the master and the first 2 subtables. I now need to ADD the third subtable to the query and am not sure how to proceed.
%-)
It appears to me that I cannot use another union so how can I get the results to include all rows from the master table that match my WHERE clause and link the correct M.ID from one of the three subtables depending on the MOPCD.
If T.MOPCD = 'BL' pull M.ID from MOPBL,
If T.MOPCD = 'CK' pull M.ID from MOPCK
otherwise pull M.ID from MOPCD

Here is my working query for reference:

SELECT T.ID, T.AMT, T.MOPCD, M.CDACCT

FROM TREQ T, MOPCD M

WHERE T.MOPID=M.ID AND ((T.ACT='DP' AND T.STAT='OKAY') OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING')) AND T.MOPCD != 'CK'

UNION

SELECT T.ID, T.AMT, T.MOPCD, M.CKACCT

FROM TREQ T, MOPCK M

WHERE T.MOPID=M.ID AND ((T.ACT='DP' AND T.STAT='OKAY') OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING')) AND T.MOPCD = 'CK'

 

[tlbroadbent]

Why can't you use another union? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[iiplusiii]

Thanks for the fast response.

If I add another UNION (I tried before writing to the list), none of the entries in the final merging includes entries from the MOPBL (because they are not in the MOPCK or MOPCD table?). I may have misinterpreted some online documentation because I understood UNION to only work with two selects. If not, can you suggest how to add the final table to my query?

 

[tlbroadbent]

Try this. I changed the JOINs to ANSI standard syntax. This syntax is not more efficient nor does it return different results. However, Microsoft has said that the old style JOIN syntax may not be supported in future versions. Some would say it is not supported in SQL 2000 because that style returns incorrect results for some outer joins.

SELECT T.ID, T.AMT, T.MOPCD, M.CDACCT
FROM TREQ T
JOIN MOPCD M
ON T.MOPID=M.ID
WHERE ((T.ACT='DP' AND T.STAT='OKAY')
OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING'))
AND T.MOPCD != 'CK'

UNION ALL

SELECT T.ID, T.AMT, T.MOPCD, M.CKACCT
FROM TREQ T
JOIN MOPCK M
ON T.MOPID=M.ID
WHERE ((T.ACT='DP' AND T.STAT='OKAY')
OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING'))
AND T.MOPCD = 'CK'

UNION ALL

SELECT T.ID, T.AMT, T.MOPCD, M.CKACCT
FROM TREQ T
JOIN MOPBL M
ON T.MOPID=M.ID
WHERE ((T.ACT='DP' AND T.STAT='OKAY')
OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING'))
AND T.MOPCD = 'CK'

Note: I also added ALL to the UNION stastement. This inhibits sorting and duplicate elimination that occurs by default in SQL. This will make the query faster! Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[iiplusiii]

Terry - You're a genious!

For the record, I changed two things in your query to get the results I was looking for:

1> On the first SELECT statement, I changed
AND T.MOPCD != 'CK'
to
AND T.MOPCD != 'CK' AND T.MOPCD != 'BL'

2> On the last SELECT statement, I changed
AND T.MOPCD = 'CK'
to
AND T.MOPCD = 'BL'