I need help with my multiple join query.

Question

I am pulling data from several tables but am experiencing a small issue. I basically have a table with biographical information on a person and a table with images of that person joined via one to many relationship. When I run the below query I get multiple rows in my data table that are all identical except for the pictures. Person A has pictures 1,2, and 3 in the picture table and when I pull the data I get a data table with person A listed 3 times with each photo.

"SELECT tblGangMemberBio.MemberID, tblGangMemberBio.VillageID, tblGangMemberBio.FirstName, tblGangMemberBio.LastName, tblGangMemberBio.MiddleName, tblGangMemberBio.Alias, tblGangMemberBio.DOB, tblGangMemberBio.Sex, tblGangMemberBio.Race,"
                + " tblGangMemberBio.Height, tblGangMemberBio.Age, tblGangMemberBio.Build, tblGangMemberBio.Hair, tblGangMemberBio.Eyes, tblGangMemberBio.Notes, tblGangMemberBio.Address, tblGangMemberBio.Zip, tblGangMemberBio.PDID, tblGangMemberBio.FBI, tblGangMemberBio.NCIC, tblGangMemberBio.LEADS, tblGangMemberBio.SID, tblGangMemberBio.MO,"
                + " tblGangMemberBio.ReportNumber, tblGangMemberBio.Active, tblGangMemberBio.HangOut, tblGangMemberBio.MemType, tblGangMemberBio.SexOffender, tblTopDies.Rank, tblGangMemberBio.Weight, tblGangs.GangName, tblProfilioPictures.Picture, tblVillages.VillageName, tblVillages.State FROM tblGangMemberBio"
                + " INNER JOIN tblTopDies ON tblGangMemberBio.MemberID = tblTopDies.MemberID"
                + " INNER JOIN tblJuncGang ON tblGangMemberBio.MemberID = tblJuncGang.MemberID"
                + " INNER JOIN tblGangs ON tblJuncGang.GangID = tblGangs.GangID"
                + " INNER JOIN tblJuncVillToGmb ON tblGangMemberBio.MemberID = tblJuncVillToGmb.MemberID"
                + " INNER JOIN tblVillages ON tblJuncVillToGmb.VillageID = tblVillages.VillageID"
                + " LEFT OUTER JOIN tblProfilioPictures ON tblGangMemberBio.MemberID = tblProfilioPictures.MemberID"
                + " WHERE tblTopDies.VillageID = @VillageID AND tblTopDies.Rank BETWEEN 1 AND 10 ORDER BY tblTopDies.Rank ASC";

 

Answer 1

PLZ POST SOME SAMPLE DATA...

YOU CAN USE GROUP BY CLAUSE WITH YOUR QUERY..IT WILL WORK

---

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Answer 2

In this scenario you will always get duplicate rows due to joins and one to many relationship ..

So how you want to represent your data...? 

Thanks,

saurabh

 

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 3

Hello guys. Basically if I have a record from tblGangMemberBio and gangbanger A has say 10 photos on file I want my query to pull only one record from tblGangMemberBio and the first photo of him from tblProfilioPictures. I will post a partial schema below. The tables with the blue highlighted

headers are the ones that are in question in my query. What I basically want is lets say I have GangMember A and he has 2 photos. I want to pull his record from tblGangMemberBio but only once! and get the first picture on file for him from tblProfilioPictures. I do not want 3 of his records from tblGangMemberBio and 3 of his photos from tblProfilioPictures. I am binding the data via .net DataTable class to a crystal report. The report is supposed to be for the top 10 bad guys selected by the user. Right now the report prints the bad guy 3 times if he has 3 photos creating duplicates in the report with the only difference being the photos. I don't care if he has more than one photo I am only interested in printing his record once and only the first photo on file once then move on to the next guy.

Answer 4

Replace:

LEFT OUTER JOIN tblProfilioPictures ON tblGangMemberBio.MemberID = tblProfilioPictures.MemberID

with:

OUTER APPLY (SELECT TOP 1 tblProfilioPictures.Picture FROM tblProfilioPictures
WHERE tblGangMemberBio.MemberID = tblProfilioPictures.MemberID ORDER BY <enter here date of the picture>) AS tblProfilioPictures

Answer 5

That worked great. I emitted the order by clause because I did not necessarily need to order by anything for having one photo each record. Thank you very much.