Re: Complex Query

> I'm trying to write a single query for this and I'm kind of stuck. I
> have a table with 3 columns:
>
> ARTICLE_ID
> AUTHOR_ID
> EDITOR_ID
>
> The AUTHOR_ID and EDITOR_ID need not be the same. I would like to get
> a count from that table, providing a user ID, of how many articles
> where they are the Author, and how many where they are the Editor.
>
> It is easy with a single ID, but the application can pass in a
> delimited string of ID's: '12,457,976'. That is where I am stuck.
> If I query and use an OR ( author_id IN (...) OR editor_id IN
> (...) ), I will get records where the AUTHOR_ID may be in my list,
> but the EDITOR_ID is not.
>
> I'm hoping this makes sense.

Totally untested, because I'm to lazy to generate a test-table:

Table name: articles
user_id = 12345

SELECT COUNT(*) AS TOTAL,
   SUM(x.AUTHOR_COUNT) AS AUTHOR_COUNT,
   SUM(x.EDITOR_COUNT) AS EDITOR_COUNT
FROM
   (SELECT a.ARTICLE_ID,

     CASE
       WHEN a.AUTHOR_ID = 12345 THEN 1
       ELSE 0
     END AS AUTHOR_COUNT,
     CASE
       WHEN a.EDITOR_ID = 12345 THEN 1
       ELSE 0
     END AS EDITOR_COUNT
   FROM ARTICLES a
     WHERE (a.AUTHOR_ID = 12345 OR a.EDITOR_ID = 12345)

Please try...

mfg
otto Received on Mon Apr 02 2012 - 16:25:46 CDT