Bruce Bruce - 2 months ago 9
MySQL Question

Combining multiple compound mysql statements

First I have the following sql statement:

SELECT * FROM members
WHERE username IN (
SELECT friend2 as username FROM list_friends
WHERE (friend1 = 'user' AND friend2 <> 'user')
UNION
SELECT friend1 as username FROM list_friends
WHERE (friend2 = 'user' AND friend1 <> 'user')
)


The above code works perfectly. It returns all rows from the 'members' table that match a given users friends list - which is created by searching the table list_friends for the given user and grabbing the friend name from either column friend1 or friend2.

Like I say, it works flawlessly.

Next I have the following sql statement:

SELECT username,
(SELECT COUNT(*) FROM sites WHERE username = 'user')
+ (SELECT COUNT(*) FROM banners WHERE username = 'user')
+ (SELECT COUNT(*) FROM stream_updates WHERE username = 'user')
AS cnt from members WHERE username = 'user'


This will combine the counts of the given rows and output the total sum of all COUNT as a column cnt.

I want to combine these two statements so I can get a result that returns friend, cnt.

Example output would be very simple:

username | cnt
----------------------
a friend | 6525
diff friend | 983
yet again | 3485


I could easily run this in two php loops, first get the names of the friends from the first query, and then use a foreach and run the second query in the loop of the first query, I am just wondering if there is a better way, or a way to otherwise combine the statements into one to get the above output.

Answer

I think you just want correlated subqueries:

SELECT username,
       ( (SELECT COUNT(*) FROM sites s WHERE s.username = f.username) +
         (SELECT COUNT(*) FROM banners b WHERE b.username = f.username) +
         (SELECT COUNT(*) FROM stream_updates su WHERE su.username = f.username)
       ) as cnt
FROM (SELECT friend2 as username
      FROM list_friends 
      WHERE friend1 = 'user' AND friend2 <> 'user'
      UNION
      SELECT friend1 as username
      FROM list_friends
      WHERE friend2 = 'user' AND friend1 <> 'user'                                     
    ) f;

Note: You don't need the members table here. You have the username from the other tables.