CODEWITHSUNDEEP
sqloracle-databaseleft-join
mistral52
mistral52

Reputation: 13

Link two tables using third one with two entries of second table that relate to one entry in first table

This might have been answered already, but I couldn't find anything.

In my Oracle DB I have two tables

S_PIPE_N

G3E_ID  |G3E_FID
380     |  1181024
2064    |  1188176

S_PCONPT_N

G3E_ID| G3E_FID| BOTTOM_HEIGHT
783   | 1181025| 253.4
4173  | 1188175| 364.51
4174  | 1188178| 366.76
17106 | 1379384| 253.11

and the table that is connecting this two

S_MANY_PCP_N

G3E_ID | G3E_FID | G3E_OWNERFID |G3E_CID
2539   | 1181025 | 1181024      |1
68507  | 1379384 | 1181024      |2
15444  | 1188178 | 1188176      |1
15448  | 1188175 | 1188176      |2

I want to get as the result of the select statement the following:

C.g3e_fid | A.bottom_height_1 | D.bottom_height_2
1181024   | 253.4             | 253.11
1188176   | 366.76            | 364.51

I tried it with following statement:

select C.G3E_FID, A.BOTTOM_HEIGHT AS "bottom_height_1", D.BOTTOM_HEIGHT "bottom_height_2" FROM S_PIPE_N C, S_MANY_PCP_N B LEFT OUTER JOIN S_PCONPT_N A ON A.G3E_FID=B.G3E_FID AND B.G3E_CID=1 LEFT OUTER JOIN S_PCONPT_N D ON A.G3E_FID=D.G3E_FID AND B.G3E_CID=2 WHERE C.G3E_FID=B.G3E_OWNERFID

Though this way I get the following:

C.g3e_fid | A.bottom_height_1 | D.bottom_height_2
1181024   | 253.4             | null
1181024   |null | 253.11
1188176   | 366.76            | null
1188176   | null           | 364.51

how can I change the statement that I get just one result per g3e_fid

Upvotes: 1

Views: 167

Answers (3)

Noel
Noel

Reputation: 10525

  SELECT A.G3E_FID,
         MAX (CASE WHEN B.G3E_CID = 1 THEN C.BOTTOM_HEIGHT END),
         MAX (CASE WHEN B.G3E_CID = 2 THEN C.BOTTOM_HEIGHT END)
    FROM S_PIPE_N a
         INNER JOIN S_MANY_PCP_N b
            ON A.G3E_FID = B.G3E_OWNERFID
         LEFT OUTER JOIN S_PCONPT_N c
            ON B.G3E_FID = C.G3E_FID
GROUP BY A.G3E_FID;

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10274

Try this:

SELECT T1.G3E_FID,T1.BOTTOM_HEIGHT  AS 'bottom_height_1' , T2.BOTTOM_HEIGHT as 'bottom_height_2'
FROM
(select C.G3E_FID,A.BOTTOM_HEIGHT
FROM S_PIPE_N C, S_MANY_PCP_N B 
LEFT OUTER JOIN S_PCONPT_N A ON A.G3E_FID=B.G3E_FID  
WHERE C.G3E_FID=B.G3E_OWNERFID AND B.G3E_CID=1) T1
FULL JOIN
(SELECT C.G3E_FID,D.BOTTOM_HEIGHT 
FROM S_PIPE_N C, S_MANY_PCP_N B 
LEFT OUTER JOIN S_PCONPT_N D ON B.G3E_FID=D.G3E_FID   
WHERE C.G3E_FID=B.G3E_OWNERFID AND B.G3E_CID=2)T2
ON T1.G3E_FID = T2.G3E_FID

Upvotes: 1

Dhaval
Dhaval

Reputation: 2861

This should work 

select C.G3E_FID, A.BOTTOM_HEIGHT AS "bottom_height_1", D.BOTTOM_HEIGHT
"b.bottom_height_2" FROM S_PIPE_N C, S_MANY_PCP_N B 
LEFT OUTER JOIN S_PCONPT_N A ON 
A.G3E_FID=C.G3E_FID AND G3E_CID=1 L
LEFT OUTER JOIN S_PCONPT_N D ON A.G3E_FID=C.G3E_FID AND 
G3E_CID=2 WHERE C.G3E_FID=B.G3E_OWNERFID and A.BOTTOM_HEIGHT IS NOT NUL

Upvotes: 1

Related Questions