Use Join or Sub Query?

Question

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!!

If you can always use joins - they are faster than subqueries. Read more about the topic stackoverflow.com/questions/2577174/join-vs-sub-query?rq=1 — glaeran
– glaeran, Commented Apr 23, 2014 at 8:52
how do you know that the address is changed ? is there any flag to determine this ? — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 23, 2014 at 8:52
Note that, technically, you should be grouping by both memberid and addressid (or, more likely, not selecting addressid at all) - otherwise, which address is being returned? — Strawberry
– Strawberry, Commented Apr 23, 2014 at 8:55
@MrCoder: What Strawberry is saying is: So far you select a random AddressID, because it's neither in the GROUP BY clause nor being aggregated by an aggregate function such as MIN, MAX and the like. EDIT: Ah, now Strawberry has edited his/her comment, so my elaboration doesn't add much anymore :-) — Thorsten Kettner
– Thorsten Kettner, Commented Apr 23, 2014 at 8:57

Patrick Hofman · Accepted Answer · 2014-04-23 13:44:08Z

1

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
;

edited Apr 23, 2014 at 13:44

answered Apr 23, 2014 at 8:53

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MrCoder Over a year ago

I have added the new query in my post. Could you please check it..?

MrCoder Over a year ago

I mean the query, I have mentioned in my post. It is the updated one now.

MrCoder Over a year ago

Thing is, I am trying to fetch the members who have changed their addresses more than 5 times. MemberAddress gives me that result. However, I now need to fetch Member and address details which are stored in member and address tables.

Patrick Hofman Over a year ago

Can you rename your count parameter? I think that might be the issue

MrCoder Over a year ago

Count parameter refers to AddressID of MemberAddress. What could be the issue with it?

|

Michael Legart · Accepted Answer · 2014-04-23 08:53:21Z

0

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

answered Apr 23, 2014 at 8:53

Michael Legart

8205 silver badges7 bronze badges

Comments

sshet · Accepted Answer · 2014-04-23 08:56:15Z

0

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

answered Apr 23, 2014 at 8:56

sshet

1,1541 gold badge6 silver badges18 bronze badges

Collectives™ on Stack Overflow

Use Join or Sub Query?

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related