|
View previous topic :: View next topic
|
| Author |
Message |
CJT
New User
Joined: 02 Aug 2007
Posts: 83
Location: Here
|
|
|
|
Hi i have 3 table join question. Please advise.
| Code: |
There are 3 tables
Table A
--------
GR_ID E_NM REC_ID
123 AAA 03
123 BBB 03
124 DDD 04
Table B
--------
CL_ID E_NM REC_ID DAT_C
Client1 AAA 03 TT
Client2 AAA 03 XX
Table C
--------
GR_ID CL_ID
123 Client1
123 Client2
124 Client3
|
I need the E_NM associated with CL_ID = Client1 only
The result set should have the following data
| Code: |
CL_ID, E_NM, REC_ID, DAT_C
Client1 AAA 03 TT
Client1 BBB 03 ----
the query used now.
Select CL_ID, E_NM, REC_ID, DAT_C from
Table A left Join
Table B on
A.E_NM = B.E_NM
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)
The result that i get is
CL_ID, E_NM, REC_ID, DAT_C
Client1 AAA 03 TT
Client2 AAA 03 XX
Client1 BBB 03 ----
|
I dont want to get the line from Client 2. please advise how the query can be modified?
[/code] |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
You where clause is wrong
| Code: |
| where GR_ID = (select GR_ID from table C where CL_ID = CLient1) |
Filter based on the field CL_ID
| Code: |
| where CL_ID = 'Client1' |
|
|
| Back to top |
|
|
CJT
New User
Joined: 02 Aug 2007
Posts: 83
Location: Here
|
|
|
|
Hi Pandora, the table A can have more GRP_ID under Client 1
| Code: |
Table A
--------
GR_ID E_NM REC_ID
123 AAA 03
123 BBB 03
124 DDD 04
125 CCC 05
Table C
---------
GR_ID CL_ID
123 Client1
125 Client1
|
I did not include the information before so that it wouldnt confuse. this is why i have used GR_ID in the where clause. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Why not try this?
| Code: |
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)
and CL_ID = 'Client1' |
|
|
| Back to top |
|
|
CJT
New User
Joined: 02 Aug 2007
Posts: 83
Location: Here
|
|
|
|
when i do that i get only the line
| Code: |
CL_ID, E_NM, REC_ID, DAT_C
Client1 AAA 03 TT
|
I need to get this line as well
| Code: |
Client1 BBB 03 ----
|
Thanks for your patience in this.
Demangled |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Please use the Preview button so that you are happy with how you post looks before using Submit. |
|
| Back to top |
|
|
Chandu C
New User
Joined: 22 Jun 2012
Posts: 2
Location: INDIA
|
|
|
|
Hi CJ
Could you please check if any info is missing regarding the requirement.
Would you mind telling the relationship between the 3 tables. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Shucks I screwed it up let me rethink |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
For the query you mentioned I got the output
I used the same set of data in the table
| Code: |
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT B.CL_ID, B.E_NM, B.REC_ID, B.DAT_C FROM 00000199
TABA A LEFT JOIN 00000299
TABB B ON 00000399
A.E_NM = B.E_NM 00000499
WHERE GR_ID = (SELECT GR_ID FROM TABC C WHERE CL_ID = 'CLIENT1'); 00000599
---------+---------+---------+---------+---------+---------+---------+---------+
CL_ID E_NM REC_ID DAT_C
---------+---------+---------+---------+---------+---------+---------+---------+
CLIENT2 AAA 03 XX
CLIENT1 AAA 03 TT
------- ---- ------ -----
------- ---- ------ -----
DSNE610I NUMBER OF ROWS DISPLAYED IS 4 |
|
|
| Back to top |
|
|
CJT
New User
Joined: 02 Aug 2007
Posts: 83
Location: Here
|
|
|
|
Hi Pandora,
the result set gives for both client 1 and client2. i need only for Client 1
| Code: |
CL_ID, E_NM, REC_ID, DAT_C
Client1 AAA 03 TT
Client1 BBB 03 ----
|
Hi Chandu, the relationship b/w the 3 tables is as follows.
The first table stores group ID with various e_nm under each group ID. the 2nd table contains more information pertaining to each E_NMs which were present in the first table.
Now 3rd table stores Group ID and CLient id. 1 groupd id can belong to more than 1 client. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Try this if it works as expected. This is untested
| Code: |
Select C.CL_ID, A.E_NM, A.REC_ID, B.DAT_C
from Table C
,Table A left Join
Table B on
A.E_NM = B.E_NM
AND B.CL_ID = Client1
WHERE C.GR_ID = A.GR_ID
AND C.CL_ID = Client1 |
Regards,
Chandan |
|
| Back to top |
|
|
CJT
New User
Joined: 02 Aug 2007
Posts: 83
Location: Here
|
|
|
|
HI Chandan,
thanks for helping. However, the query did not give the desired result.
I tried this query out and it worked.
| Code: |
Select CL_ID, E_NM, REC_ID, DAT_C from
Table A left Join
Table B on
A.E_NM = B.E_NM and B.CL_ID = Client1
where GR_ID = (select GR_ID from table C where CL_ID = CLient1)
|
Thank you Chandan, pandora for your help on this. [/code] |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Can you share the output of query posted by me?
I am just curious what are the wrong results
and for your query just change like below
| Code: |
Select CL_ID, E_NM, REC_ID, DAT_C from
Table A left Join
Table B on
A.E_NM = B.E_NM and B.CL_ID = Client1
where GR_ID IN (select GR_ID from table C where CL_ID = CLient1) |
So that with IN clause it will work even if Subquery gives mutiple rows
Regards,
Chandan |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select *
from Table_C C
INNER JOIN TABLE_A A on A.GR_ID = C.GR_ID
LEFT JOIN TABLE_B B on B.CL_ID = C.CL_ID and B.E_NM = A.E_NM
where C.CL_ID = 'Client1' |
|
|
| Back to top |
|
|
|
|
