Joined: 22 Feb 2007 Posts: 8 Topics: 3 Location: Roswell, GA
Posted: Fri Feb 23, 2007 7:40 am Post subject: Join 2 tables
Problem: I have to do a select on two tables - one that has to join back to itself. I can't code it right.
Table A looks like this: aa,ab,ac,ad...ay,az
Table B looks like this: aa, bb, bc, bd ...
The result table should look like this:
Code:
aa,
ab,
bb,
bc,
ab(from A row where aa = ay)
ab(from A row where aa = az),
ae,
af,
ag.
I tried a number of selects but can't even get past edit.
First I used a case statement:
Code:
Select A.ab,A.ac,B.bb,B.bc,
Case
when ay = ' ' then ' '
else select ab from c.A where c.aa = a.xy.
End case
, Case
when az = ' ' then ' '
else select ab from c.A where c.aa = a.xz.
End case
, ae , af, ag
from a.A b.B
Then I tried Union All with a subselect on the selected set.
Code:
Select A.ab,A.ac,B.bb,B.bc,ay,az,ae,af from a.A B.b Union all
case
if ay null and az null
Select A.ab,A.ac,B.bb,B.bc,' ' , ' ',ae,af from A A, B B
end case
case
if ay null and az not null
Select A.ab,A.ac,B.bb,B.bc,' ' ,
select c.ab from ' ',ae,af from A A, B B
end case
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Fri Feb 23, 2007 9:58 am Post subject:
Table A has the following columns and denoted X
Code:
COL1
COL2
COL3
COL4
Table B has the following columns and denoted Y
Code:
COL1
COL2
COL3
COL4
The following sql will give you the desired results.
Code:
SELECT X.COL2
,CASE WHEN X.COL3 IS NULL AND X.COL4 IS NULL
THEN CHAR('-')
WHEN SUBSTR(X.COL3,1,2) = 'AA' AND X.COL4 IS NULL
THEN CHAR('-')
WHEN X.COL3 IS NULL AND SUBSTR(X.COL4,1,2) = 'AA'
THEN CHAR('AB') || SUBSTR(X.COL4,3,1)
ELSE X.COL3 END
,CASE WHEN X.COL4 IS NULL AND X.COL3 IS NULL
THEN CHAR('-')
WHEN SUBSTR(X.COL4,1,2) = 'AA' AND X.COL3 IS NULL
THEN CHAR('-')
WHEN X.COL4 IS NULL AND SUBSTR(X.COL3,1,2) = 'AA'
THEN CHAR('AB') || SUBSTR(X.COL3,3,1)
ELSE X.COL4 END
,Y.COL2
,Y.COL3
FROM Tablea X
,Tableb Y
WHERE X.COL1 = Y.COL1
;
Joined: 22 Feb 2007 Posts: 8 Topics: 3 Location: Roswell, GA
Posted: Fri Feb 23, 2007 12:20 pm Post subject:
THX, THIS IS WHAT I'M CODING... WILL IT WORK?
Code:
SELECT DISTINCT
A.ID, A.NBR, B.NM, B.LOB, A.OPEN, A.CLS
,CASE WHEN A.TX IS NULL THEN CHAR('-')
WHEN A.TX = C.ID THEN C.NBR
END
,CASE WHEN A.FR IS NULL THEN CHAR('-')
WHEN A.FR = C.ID THEN C.NBR
END
, A.TYP_CD , A.SZ_CD
FROM STR A , HIER B , STR C
WHERE A.ID = B.ID
Joined: 22 Feb 2007 Posts: 8 Topics: 3 Location: Roswell, GA
Posted: Sat Feb 24, 2007 2:17 pm Post subject: almost works
There's a third table involved and I wound up with a 5,000,000 row result so I added distinct and it's now down to almost the right result but I am getting duplicate rows, only, when the ay,az are not null and then I get botht the row with null and with the right aa value?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
