Select distinct U.fullname,U.userid, U.email, JA.advtitle,JA.advid, PInf.country,PInf.city, JO.digitalsignature,JO.doc from tblUser as U left join tblPersonalInfo as PInf ON U.userid = PInf.userid left join tblApplication as App On App.userid = U.userid left join tblJobAdv as JA On JA.advid = App.advid left join tblJobOffer as JO On U.userid = JO.userid where JA.userId IN (select userid from tblUser where email = 'waleedkhan123@yahoo.com') and digitalsignature=1
Most likely due to the left joins and having duplicate records in one of the tables. You'll need to narrow down your select statement and take it one step at a time to get to the offending table.
select distinct a.* from (Select distinct U.fullname,U.userid, U.email, JA.advtitle,JA.advid, PInf.country,PInf.city, JO.digitalsignature,JO.doc from tblUser as U left join tblPersonalInfo as PInf ON U.userid = PInf.userid left join tblApplication as App
On App.userid = U.userid left join tblJobAdv as JA On JA.advid = App.advid left join tblJobOffer as JO On U.userid = JO.userid where JA.userId IN (select userid from tblUser where email = 'waleedkhan123@yahoo.com') and digitalsignature=1) a;
Hunain Hafee...
Member
238 Points
639 Posts
query shows each record 2 times
Feb 09, 2013 05:37 PM|LINK
why this query returns each record twice ?
MetalAsp.Net
All-Star
112718 Points
18367 Posts
Moderator
Re: query shows each record 2 times
Feb 09, 2013 05:56 PM|LINK
Most likely due to the left joins and having duplicate records in one of the tables. You'll need to narrow down your select statement and take it one step at a time to get to the offending table.
Hunain Hafee...
Member
238 Points
639 Posts
Re: query shows each record 2 times
Feb 09, 2013 06:04 PM|LINK
so what would be the technical solution in my query's case , exactly ?
wmec
Contributor
6532 Points
3321 Posts
Re: query shows each record 2 times
Feb 11, 2013 02:56 AM|LINK
Try
select distinct a.* from (Select distinct U.fullname,U.userid, U.email, JA.advtitle,JA.advid, PInf.country,PInf.city, JO.digitalsignature,JO.doc from tblUser as U left join tblPersonalInfo as PInf ON U.userid = PInf.userid left join tblApplication as App On App.userid = U.userid left join tblJobAdv as JA On JA.advid = App.advid left join tblJobOffer as JO On U.userid = JO.userid where JA.userId IN (select userid from tblUser where email = 'waleedkhan123@yahoo.com') and digitalsignature=1) a;
HuaMin Chen