Saturday, February 16, 2013 5:16 AM
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";
Saturday, February 16, 2013 6:28 AM
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
Saturday, February 16, 2013 11:23 AM
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...?
Saturday, February 16, 2013 4:12 PM
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.
Saturday, February 16, 2013 11:00 PM
LEFT OUTER JOIN tblProfilioPictures ON tblGangMemberBio.MemberID = tblProfilioPictures.MemberID
OUTER APPLY (SELECT TOP 1 tblProfilioPictures.Picture FROM tblProfilioPictures
WHERE tblGangMemberBio.MemberID = tblProfilioPictures.MemberID ORDER BY <enter here date of the picture>) AS tblProfilioPictures
Sunday, February 17, 2013 4:55 PMThat 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.