Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: How to query this?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •