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 http://stackoverflow.com/questions/2577174/join-vs-sub-query?rq=1 — glaeran, Apr 23 '14 at 08:52
how do you know that the address is changed ? is there any flag to determine this ? — Abhik Chakraborty, Apr 23 '14 at 08: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, Apr 23 '14 at 08: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, Apr 23 '14 at 08:57
I dont need to determine current one. Will count the number of addresses in MemberAddress which will mean customer has had lived at that many addresses. Please check my query and let me know where am going wrong now. — MrCoder, Apr 23 '14 at 10:39

Patrick Hofman · Accepted Answer · 2014-04-23T13:44:08.067

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 '14 at 13:44

answered Apr 23 '14 at 08:53

Patrick Hofman

153,850
22
249
325

I have added the new query in my post. Could you please check it..? – MrCoder Apr 23 '14 at 10:40
I mean the query, I have mentioned in my post. It is the updated one now. – MrCoder Apr 23 '14 at 10:42
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. – MrCoder Apr 23 '14 at 10:49
Can you rename your count parameter? I think that might be the issue – Patrick Hofman Apr 23 '14 at 10:58
Count parameter refers to AddressID of MemberAddress. What could be the issue with it? – MrCoder Apr 23 '14 at 11:01
Count is a preserved keyword. You shouldn't use it as column name. – Patrick Hofman Apr 23 '14 at 11:27
Also, you need `c.MajorMunicipality` in your `group by`. – Patrick Hofman Apr 23 '14 at 11:32
@MrCoder: any luck in fixing this? – Patrick Hofman Apr 23 '14 at 13:35
@MrCoder: Try the last sql in my answer. – Patrick Hofman Apr 23 '14 at 13:44

score 0 · Answer 2 · answered Apr 23 '14 at 08:53

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 '14 at 08:53

Michael Legart

800
4
7

score 0 · Answer 3 · answered Apr 23 '14 at 08:56

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 '14 at 08:56

sshet

1,152
1
6
15

Use Join or Sub Query?

3 Answers3