1

My query is only showing a subset of records of people who have messages for me whereas I want to return a list of all users wether they have messages for me or not along with the count (or true/false, 0,1)

SELECT attendee.id, attendee.firstName, attendee.lastName, count(attendee_chat.to) 
from attendee_chat
INNER JOIN attendee ON attendee.id = attendee_chat.to
INNER JOIN chat ON attendee_chat.id = chat.attendee_chat_id
WHERE attendee.id <> 1
GROUP BY attendee_chat.to;

picture is worth a thousand words.

enter image description here

user2727195
  • 7,122
  • 17
  • 70
  • 118

2 Answers2

0

If you want to do a join on records even if they have no matching element(s) in the other relation, you probably want an OUTER JOIN instead of an INNER JOIN.

MySQL: Quick breakdown of the types of joins

Community
  • 1
  • 1
jaynp
  • 3,275
  • 4
  • 30
  • 43
0

Put the attendee table first in the FROM, and then left join to the other tables that may not have records.

SELECT attendee.id, attendee.firstName, attendee.lastName, count(attendee_chat.to) 
from attendee 
LEFT JOIN attendee_chat ON attendee_chat.to = attendee.id
LEFT JOIN chat ON attendee_chat.id = chat.attendee_chat_id
WHERE attendee.id <> 1
GROUP BY attendee_chat.to;

I would avoid doing a FULL OUTER JOIN as it is not necessary and may lead to some unexpected results.

EDIT: Try this. Remove the chat table as you aren't selecting from it anyway, and group on the fields from the primary table attendee, rather than the on tables that may not contain any data.

SELECT attendee.id, attendee.firstName, attendee.lastName, count(attendee_chat.to) 
from attendee 
LEFT JOIN attendee_chat ON attendee_chat.to = attendee.id
WHERE attendee.id <> 1
GROUP BY attendee.id, attendee.firstName, attendee.lastName;

EDIT2: After discussion of requirements that he wants a count of chats that were to him (#1) 

SELECT attendee.id, attendee.firstName, attendee.lastName, 
(SELECT count(1) FROM attendee_chat 
   WHERE attendee_chat.from = attendee.id and  attendee_chat.to = 1) as chatcount
 from attendee ;

EDIT3: After comment about external ID

SELECT attendee.id, attendee.firstName, attendee.lastName, attendee.attendee_id,
(SELECT count(1) FROM attendee_chat 
    INNER JOIN attendee a2 ON a2.id = attendee_chat.toid
    WHERE attendee_chat.fromid = attendee.id and a2.attendee_id = 123
) as chatcount
FROM attendee ;

P.S. I've changed from to fromid and to -> toid as it is dangerous using reserved words as object names ;-) SQL Fiddle here

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
  • ok, but then it's returning the same result set, I want list of all attendees even if they have no messages from me – user2727195 Nov 11 '14 at 23:33
  • awesome, question, why there are two more fields in the group by – user2727195 Nov 11 '14 at 23:49
  • Because quite often if you want to select the fields and you don't have an aggregate function on them, you have to include them in the GROUP BY clause. Some version of SQL will throw an error if you don't. – Dijkgraaf Nov 11 '14 at 23:50
  • I see, so if aggregate function like count is not present, having a single field in the group by throws an error, cool that you picked up that I wasn't selecting anything from chat table – user2727195 Nov 11 '14 at 23:54
  • Even if the count is present and you don't have MAX, MIN, COUNT, AVERAGE etc. around all the other fields that you are selecting and not in the GROUP BY clause, it will error in some versions of SQL. – Dijkgraaf Nov 11 '14 at 23:56
  • could i use then just two fields instead of 3? I'm having some issues on my test records returned, not sure, am checking – user2727195 Nov 12 '14 at 00:10
  • I've added a picture, it's like list of attendees, and wanna display a number next to them, there's some problem with the results, I think we are confused with attendee_chat.to & attendee_chat.id for the join – user2727195 Nov 12 '14 at 00:22
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/64748/discussion-between-dijkgraaf-and-user2727195). – Dijkgraaf Nov 12 '14 at 00:36
  • another point that I didn't mention to not to confuse people, I've a 3rd party id integrated into the system, i.e. I don't receive primary key on the dynamic page, i receive and use 3rd party key to retrieve the primary key, I modified your answer and asking you while expecting if there's a better solution that I don't know of... `SELECT attendee.id, attendee.firstName, attendee.lastName, (SELECT count(1) FROM attendee_chat WHERE attendee_chat.from = attendee.id AND attendee_chat.to = (SELECT id from attendee WHERE attendee_id = attendee.attendee_id)) as chat from attendee` – user2727195 Nov 12 '14 at 01:26
  • You can just select the external id in the list. – Dijkgraaf Nov 12 '14 at 02:20
  • sorry didn't get you, can you please explain more, I nested another subquery to get the id – user2727195 Nov 12 '14 at 02:22
  • great, now using your example, if I say `attendee.id` is not known and we need to determine it from attendee.attendee_id passed (123, 456 or 789) and use it in the above query – user2727195 Nov 12 '14 at 02:27
  • Just add it as a where clause. – Dijkgraaf Nov 12 '14 at 02:28
  • I added but it didn't work, can you please copy/paste and execute this in your fiddle to understand, `SELECT attendee.id, attendee.firstName, attendee.lastName, (SELECT count(1) FROM attendee_chat WHERE attendee_chat.from = attendee.id AND attendee_chat.to = (SELECT id from attendee WHERE attendee_id = 123)) as chat from attendee` – user2727195 Nov 12 '14 at 02:33
  • saw but not actually, please put 123 (only) inside the `IN` clause, 123 is actually me, imagine I don't know my `id`, i've to determine my id from 123 and then run the query, (think of 123 as 3rd party primary key, foreign key in attendee table) – user2727195 Nov 12 '14 at 02:38
  • cool, I see a2 alias, it's because the subquery sees the attendee table in the outer table – user2727195 Nov 12 '14 at 03:02
  • Yes, it would see the outer query – Dijkgraaf Nov 12 '14 at 03:03