How to query this?

jack007 · #1 (**permalink**) 02-11-12, 08:22

Hi all,

I am new here and want to know how to query the following:

I have tables users and a table news

the fields in news are text, photo, video

in text, photo and video columns the user_id from the users goes.

Now I want to display the list of all users order by the number of records they have in news table.

LIKE:
SELECT COUNT(*) as num FROM news WHERE text='$id_user' OR photo='$id_user' OR video='$id_user'

this query will give us which user has maximum number of records in news tables based on text, photo and video.

Actually I want to display the users in descending order based on the sum of of text OR photo OR video column.

Any help for this will highly be appreciated.

regards

r937 · #2 (**permalink**) 02-11-12, 09:36

Code:

SELECT users.user_id
     , SUM(CASE WHEN news.user_id = `text` THEN 1 END +
           CASE WHEN news.user_id = photo  THEN 1 END +
           CASE WHEN news.user_id = video  THEN 1 END ) AS num 
  FROM users
LEFT OUTER
  JOIN news 
    ON news.user_id IN ( `text` , photo , video )
   AND news.user_id = users.userid
GROUP
    BY users.user_id
ORDER
    BY num DESC

Internet Services

Web Development

Digital Marketing

Consumer Tech