Retrieve Columns By joining Four Tables

[mansi sharma]

--Que-5) Select CTM_NBR,LIST_CO DE,ADR_CDE,ADR_ FLAG
--From table A,B,C,D by linking the tables where
--the LIST_CODE=18.

TABLE A Has Column CTM_NBR
TABLE B Has Columns CTM_NBR , ADR_CDE , ADR_FLAG
TABLE C Has Columns LIST_CODE , CTM_NBR, ADR_CDE
TABLE d Has Column LIST_CODE



select CTM_NBR,LIST_CO DE,ADR_CDE,ADR_ FLAG from A,B,C,D
where A.CTM_NBR = B.CTM_NBR
AND
B.CTM_NBR = C.CTM_NBR
AND
C.LIST_CODE=D.L IST_CODE
AND
D.LIST_CODE=18

I have tried the aboce Query but its not working,Plz reply asap,Its Urgent.

[Delerna]

The types of joins you use depends on the data you have in your tables

But it should look something like this
[code=sql]
select CTM_NBR,LIST_CO DE,ADR_CDE,ADR_ FLAG
from A
left join B on A.CTM_NBR = B.CTM_NBR
left join C on A.CTM_NBR = C.CTM_NBR
and B.CTM_NBR=C.CTM _NBR
and B.ADR_CDE=C.ADR _CDE
left join D on C.LIST_CODE=D.L IST_CODE
where D.LIST_CODE=18
[/code]

NOTE
====
1)Join will only return records where there is a match in both tables
2)Left Join will return every record from the first table
regardless of whether there is a match in the second table

There are other types of joins. Check the help files

[mansi sharma]

Actually delerna it was a interview Question of company I have faced...dats that I have also no idea abt the tables,So I Didn't know where to use JOIN oR LEFT JOIN.

[madankarmukta]

Hi Mansi,
I Think the query

select CTM_NBR,LIST_CO DE,ADR_CDE,ADR_ FLAG from A,B,C,D
where A.CTM_NBR = B.CTM_NBR
AND
B.CTM_NBR = C.CTM_NBR
AND
C.LIST_CODE=D.L IST_CODE
AND
D.LIST_CODE=18
is perfectly fine except you should give the alias to each table n then use alias to access their respective column. However can u pls tell me is query giving any sysntax error or not returning any data...??

It it is not returning any data try to use LTrim(RTrim(Upp er(C.LIST_CODE) ))=LTrim(RTrim( Upper(D.LIST_CO DE))) or

LTrim(RTrim(Low er(C.LIST_CODE) ))=LTrim(RTrim( Lower(D.LIST_CO DE)))
this may be the solution.

However the solution given by Delerna is also fine.

Regards,
Mukta