Need to fetch data from 2 tables using Join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to fetch data from 2 tables using Join

Author

Topic

wickedtaurian
Starting Member

1 Post

Posted - 2009-10-04 : 07:28:47

Hi All i am trying to fetch results from 2 tables, and using a third table which acts as a M:M relationship between these two.
The Table A has data of group A
--GAMemberId
--GAMemFN
--GAMemSN

Table B has data of group B
--GBMemberId
--GBMemFN
--GBMemSN

Table C has the PK of table A and B as attributes.
say GAMemID GBMemID
T1 100
T2 300, 400
T3 200, 400

Now i have to fetch Group A Member details and corresponding Group B Member details who are assigned to each other. Also i have to include the rest Group A Member details to whom no Group B members are assigned.

I am trying to do it in the following way:-
select a.GAMemberID, a.GAMemFN, a.GAMemSN, b.GBMemberID, b.GBMemFN, b.GBMemSN
from tableA a, tableB b, tableC c
WHERE NOT a.GAMemberID = c. GAMemberID
AND
b.GBMemberID = c.GBMemberID
Group By a.GAMemberID,a.GAMemFN, a.GAMemSN, b.GBMemberID, b.GBMemFN, b.GBMemSN
order by a.GAMemFN;

i am getting wrong results, it shows the GAMemberId required but the number of results are very big. I am trying to do it using a left outer join but no success. Kindly Help me to know where i am wrong.

Best Regards,
AB

AB
Analyst QA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 14:05:05

sorry your question is not clear. can you illustrate your scenario with data in below format?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-05 : 14:11:40

does this work for you?


SELECT   a.gamemberid, 
         a.gamemfn, 
         a.gamemsn, 
         b.gbmemberid, 
         b.gbmemfn, 
         b.gbmemsn 
FROM     tablea a 
         LEFT OUTER JOIN tablec c 
           ON a.gamemberid = c.gamemid 
         LEFT OUTER JOIN tableb b 
           ON b.gbmemberid = c.gbmemid 
ORDER BY a.gamemfn;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources