CODEWITHSUNDEEP
mysqlsql
MrCoder
MrCoder

Reputation: 103

Use Join or Sub Query?

I have 3 tables: Student, Address and StudentAddress.

Student stores all the students, address stores all the address details while StudentAddress resolves many to many relationship between Student and Address. This table stores details of student who have lived in more than one addresses.

I am trying to list the Names and address details of a student who has changed his address more than 5 times.

SELECT a.StudentID, CONCAT(b.FirstName + " " + b.LastName), c.MajorMunicipality,
       COUNT(a.AddressID) AS count 
FROM StudentAddress a 
INNER JOIN Member b 
        ON a.StudentID = b.StudentID 
INNER JOIN Address c 
        ON a.AddressID = b.AddressID
GROUP BY a.StudentID, a.AddressID 
HAVING count > 5;

This query has issues with joining. Please help!!

Upvotes: 0

Views: 95

Answers (3)

Patrick Hofman
Patrick Hofman

Reputation: 156978

I would prefer join since it gives your more possibilities to use the result for your second query.

To help you narrowing down the actual result set, try something like this:

select a.MemberID
,      a.AddressID
,      COUNT(a.AddressID) as countAddress
from   MemberAddress a
group
by     a.MemberID
,      a.AddressID
having countAddress > 3
;

EDIT:

Try this:

select a.memberid
,      concat(b.firstname + " " + b.lastname)
,      c.majormunicipality
,      count(a.addressid) as countAddresses
from   memberaddress a 
join   member b 
on     a.memberid = b.memberid 
join   address c 
on     a.addressid = b.addressid
group
by     a.memberid
,      concat(b.firstname + " " + b.lastname)
,      c.majormunicipality
having count > 5
;

Upvotes: 1

sshet
sshet

Reputation: 1160

Probably you should use HAVING Clause

      SELECT a.MemberID,
      a.AddressID,
      COUNT(a.AddressID) AS COUNT
      FROM MemberAddress a
      GROUP BY a.MemberID;
      HAVING COUNT >=3

Upvotes: 0

Michael Legart
Michael Legart

Reputation: 820

To filter them:

select a.MemberID,a.AddressID,COUNT(a.AddressID) as count 
from MemberAddress a group by a.MemberID HAVING COUNT(a.AddressID) > 3

Upvotes: 0

Related Questions