CODEWITHSUNDEEP
sql
Amir
Amir

Reputation: 199

I need help writing a complex SELECT statement

i am trying to write an sql statement that will select items from one table and order it by the result of the other table..

the 2 tables are:

events: id, name

attendance: user, event

ive got this statement:

SELECT * 
FROM `attendance` 
WHERE event='1' 
  AND user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153)

I am using this to get the number of friends that coming to event number 1.

now I want to combine that statement with another one to create a statement that select all the events, and order it by the number of friends that going... how can i write that statement?

Thank you very much, Amir.

Upvotes: 0

Views: 597

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 754450

Assuming that the list of numbers is the set of friends you're interested in (it would be better to store them in a table - that's how you record information in a database), then:

SELECT e.id, e.name, COUNT(*) AS friends
  FROM attendance AS a
  JOIN event      AS e ON a.event = e.id
 WHERE a.user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153)
 GROUP BY e.id, e.name;

Complexity, of course, is in the eye of the beholder - this is not actually complex. Note that as written, it does not list events to which none of the friends go. If you want that, you need a LEFT OUTER JOIN and you need the 'friend filter' earlier than the main WHERE clause:

SELECT e.id, e.name, COUNT(user) AS friends
  FROM event AS e
  LEFT OUTER JOIN
       (SELECT user, event
          FROM attendance
         WHERE user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153)
       ) AS a
    ON a.event = e.id
 GROUP BY e.id, e.name;

The COUNT(user) aggregate returns zero if all the rows in a group contain NULL in the user column.

Untested SQL.

Upvotes: 0

Christoph
Christoph

Reputation: 2004

You could also use a join like:

SELECT events.id, COUNT(attendance.user) AS attending
FROM attendance INNER JOIN events ON attendance.event = events.id
WHERE (attendance.user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153))
GROUP BY events.id
ORDER BY attending DESC

Upvotes: 1

Tomas Aschan
Tomas Aschan

Reputation: 60634

I would try something like this:

SELECT
    id,
    name,
    (SELECT COUNT(*) FROM attendance att WHERE att.event = ev.id) AS attending
FROM
    event ev
ORDER BY
    attending DESC

However, although I would try something like that, fact is I haven't, so this might not work as is.

Upvotes: 3

Related Questions