| View previous topic :: View next topic |
| Author |
Message |
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
 Posted: Thu Dec 06, 2007 5:14 pm Post subject: JOIN without repeating the rows for smaller table |
 |
|
table1;
| Code: | ID Name seq number
11 AAA 1
22 BBB 1
33 CCC 1
44 DDD 1
55 EEE 1 |
table2:
| Code: | ID indicator
11 Y
22 N
33 N
44 N
55 Y |
table3:
| Code: | ID Amount
11 100
11 100
11 100
22 200
22 200
33 300
44 400
44 400 |
Objective:
I want all the rows from table1, that have matching row in both table2 and table3.
SOLUTION BY JOIN:
Since table3 have more that one matching row for a rows in table1, result of the join will be like this:
a normal JOIN would come like this: | Code: |
SELECT TABLE1-COLUMNS
FROM TABLE1
JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
JOIN TABLE3
ON TABLE2.ID=TABLE3.ID; |
RESULT:
| Code: | ID Name Seq number
11 AAA 1
11 AAA 1
11 AAA 1
22 BBB 1
22 BBB 1
33 CCC 1
44 DDD 1
44 DDD 1 |
I want the result of JOIN like this (join by ID), I dont want to repeat the rows on table1, if there are more rows on table3 that match with TABLE1. In fact, I just wanted to make sure the rows are present in right table.
| Code: | ID Name Seq number
11 AAA 1
22 BBB 1
33 CCC 1
44 DDD 1 |
ALTERNATIVE Solution:
For this am doing below: | Code: |
SELECT ID, NAME, SEQNUMBER
FROM TABLE1
JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
AND EXISTS
(SELECT ID FROM TABLE2
WHERE TABL2.ID=TABLE3.ID)
|
Above query will return the rows from table1, if they have corresponding Id present in table3. (of course, I am okay to repeat the resultant rows if table2 has more rows that match with one table1 row)
Issue:
Now I got another issue here. I can not do further JOINs after I use the EXISTS in a join.
see below: If I want to join another table called table4, I get syntax error. DB2 doesn't allow me to use a join after using exists. | Code: |
SELECT ID, NAME, SEQNUMBER
FROM TABLE1
JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
AND EXISTS
(SELECT ID FROM TABLE2
WHERE TABL2.ID=TABLE3.ID)
JOIN TABLE4
ON TABLE1.NAME=TABLE4.NAME
JOIN TABLE5
ON TABLE4.CITY=TABLE5.CITY
|
Can't I perform JOIN after use of 'EXISTS'? My situation forces me to join table4 and 5 after 2 and 3.
Alternative: I did below to avoid syntax error: | Code: |
SELECT ID, NAME, SEQNUMBER
FROM TABLE1
JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
AND EXISTS
(SELECT ID FROM TABLE2
WHERE TABL2.ID=TABLE3.ID)
,TABLE4
,TABLE5
WHERE TABLE1.NAME=TABLE4.NAME
AND TABLE4.CITY=TABLE5.CITY
|
As you see above, I mentioned the JOIN predicates in WHERE caluse. This is acceptable only if we want a INNER JOIN. I think I can't do this for OUTER JION. So, any alternative to achieve OUTER JOIN?
Please dont hesitate to ask em any questions. Also let em know if i miss any information.
_________________
Thanks |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Dec 06, 2007 5:54 pm Post subject: |
|
|
Sarangadhar,
I would keep it simple like this. untested sql
| Code: |
SELECT TABLE1-COLUMNS
FROM TABLE1
WHERE ID IN (SELECT DISTINCT ID FROM TABLE2)
AND ID IN (SELECT DISTINCT ID FROM TABLE3)
;
|
Hope this helps...
Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Thu Dec 06, 2007 6:01 pm Post subject: |
|
|
Kolusu,
What if am looking for more than one column. Instead of ID, it is 4 columns that I wanted to join on.
Also the join participants between table1 and 2 are different from the participants between table2 and table3.
_________________
Thanks |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Thu Dec 06, 2007 6:12 pm Post subject: |
|
|
Kolusu,
Let me give more specific details:
Table1 and table2 needs a JOIN, as there are some extrax columns in table2 than in table1 that I need to use in JOIN between table2 and table3.
e.g:
table1
ID
table2:
ID
NAME
table3:
ID
NAME
CITY
JOIN table1 and table2 by ID. (one Id might contain multiple NAMEs)
from the resultant, I need to find if every ID and NAME from JOIN is present in table3. Assume ID is a composite key of 3 columns.
_________________
Thanks |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Thu Dec 06, 2007 6:15 pm Post subject: |
|
|
And as I said earlier, I need to perform LEFT JOIN to the above resultant of table1, 2, and 3
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Dec 06, 2007 7:38 pm Post subject: |
|
|
Sarangadhar,
I like to keep it simple. I cannot really test but based on your last post, I would do something like this
| Code: |
Select *
from tab3
where(id,name) in (Select id
,name
from tab2
where id in (Select id from tab1));
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Thu Dec 06, 2007 8:06 pm Post subject: |
|
|
Kolusu,
This gives all the rows in table3 that have matching rows in table2 adn 1. There are possibly duplicates. I dont want them and I dont want to do a distinct since there are more than million rows.
your query will result belwo, as there are more than onw rows int able 3 for each row in table 2 or 1.
| Code: | ID Name Seq number
11 AAA 1
11 AAA 1
11 AAA 1
22 BBB 1
22 BBB 1
33 CCC 1
44 DDD 1
44 DDD 1 |
But, I want like this:
| Code: | ID Name Seq number
11 AAA 1
22 BBB 1
33 CCC 1
44 DDD 1 |
_________________
Thanks |
|
| Back to top |
|
|
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
| Posted: Fri Dec 07, 2007 12:34 am Post subject: |
|
|
Sarangadhar,
| Quote: |
This gives all the rows in table3 that have matching rows in table2 adn 1. There are possibly duplicates. I dont want them and I dont want to do a distinct since there are more than million rows.
|
If you are more specific on the results than the procedure, I would suggest you to use DSNTIAUL utility to execute the query and SORT to strip off the duplicates.
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay) |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Fri Dec 07, 2007 1:12 am Post subject: |
|
|
Vivek1938,
Unfortunately, I'll have to run these queries from within a JAVA application, hence I couldn't us the MVS utilities from JAVA.
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 07, 2007 11:59 am Post subject: |
|
|
Sarangadhar,
If your final output is from table 3 which has duplicates then you ought to use DISTINCT whether you use subqueries or joins.
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Fri Dec 07, 2007 1:02 pm Post subject: |
|
|
| A different option would be to utilize a stored procedure. Declare a global temporary table. Open your cursor on table 1. Issue the SELECTs to table 2 and 3 and if they satisfy your requirements, INSERT a row to the temp table. Then open a cursor on the temp table and return that as the results set to your caller. Seems like a lot of work to avoid a SELECT DISTINCT, but I suppose I could see some cases where this would improve the performance by eliminating a sort on a very large intermediate results set. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
