sandeep nagabhairava sandeep nagabhairava - 6 months ago 8
SQL Question

Select all columns from two tables grouping by all columns in table1 and specific column in table2

My table structure is like below

TblMemberInfo | TblCarInfo
MemberID Name | Id MemberId CarNumber
1 Sandeep | 1 2 1234
2 Vishal | 2 1 1111
3 John | 3 4 2458
4 Kevin | 4 2 1296
5 Devid | 5 4 7878
| 6 3 4859


I need to query for select all from TblMemberInfo,TblCarInfo where Count(MemberId)=1

MemberId Name CarNumber
1 Sandeep 1111
3 John 4859

Answer

Here is one method:

select mi.MemberID, mi.Name, min(CarNumber) as CarNumber
from TblMemberInfo mi join
     TblCarInfo ci
     on mi.MemberID = ci.MemberID
group by mi.MemberID, mi.Name
having count(*) = 1;

This works, because with only one row in the group, the min() returns the right value.

And alternative approach uses not exists:

select mi.MemberID, mi.Name, ci.CarNumber
from TblMemberInfo mi join
     TblCarInfo ci
     on mi.MemberID = ci.MemberID
where not exists (select 1
                  from TblCarInfo ci2
                  where ci2.MemberID = ci.MemberID and ci2.id <> ci.id
                 );
Comments