By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Retrieve Columns By joining Four Tables

P: 22
--Que-5) Select CTM_NBR,LIST_CODE,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_CODE,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.LIST_CODE
AND
D.LIST_CODE=18


I have tried the aboce Query but its not working,Plz reply asap,Its Urgent.
Apr 9 '08 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
The types of joins you use depends on the data you have in your tables

But it should look something like this
Expand|Select|Wrap|Line Numbers
  1. select CTM_NBR,LIST_CODE,ADR_CDE,ADR_FLAG 
  2. from A
  3. left join B on A.CTM_NBR = B.CTM_NBR
  4. left join C on A.CTM_NBR = C.CTM_NBR 
  5.            and B.CTM_NBR=C.CTM_NBR
  6.            and B.ADR_CDE=C.ADR_CDE
  7. left join D on C.LIST_CODE=D.LIST_CODE
  8. where D.LIST_CODE=18
  9.  
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
Apr 10 '08 #2

P: 22
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.
Apr 10 '08 #3

100+
P: 293
Hi Mansi,
I Think the query

select CTM_NBR,LIST_CODE,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.LIST_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(Upper(C.LIST_CODE)))=LTrim(RTrim(Upper (D.LIST_CODE))) or

LTrim(RTrim(Lower(C.LIST_CODE)))=LTrim(RTrim(Lower (D.LIST_CODE)))
this may be the solution.

However the solution given by Delerna is also fine.

Regards,
Mukta
Apr 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.