Article:
  SQL Subqueries
Subject:   A little SQL Subquery help, please?
Date:   2004-12-14 22:02:03
From:   LFaler
This explanation is great, but I'm having a problem applying it to something.


I have a table that contains 4 columns. "ID", "Name", "Yes/No", "Date". There is only one record per day per name. What I would like is for it to return the information as follows:


___________________________________
| Name | Date 1 | Date 2 | Date 3 |
-------------------------------------
| Name 1 | Yes/No | Yes/No | Yes/No |
| Name 2 | Yes/No | Yes/No | Yes/No |
| Name 3 | Yes/No | Yes/No | Yes/No |


I've been toying with the following query, but it isn't returning it right. How should I write it?


"SELECT DISTINCT a.Name, (SELECT YesNo FROM L_Mail_Log b WHERE Date=1/2/2004 AND Name = a.Name) AS [1/2/2004], (SELECT YesNo FROM L_Mail_Log b WHERE Date=1/3/2004 AND Name = a.Name) AS [1/3/2004], (SELECT YesNo FROM L_Mail_Log b WHERE Date=1/4/2004 AND Name = a.Name) AS [1/4/2004] FROM L_Mail_Log a"

Full Threads Oldest First

Showing messages 1 through 1 of 1.

  • A little SQL Subquery help, please?
    2004-12-21 12:34:42  msantoyo [View]

    "LFALER" You can try soemthing like this (only works if you have an exact number of days.

    For the following example I am altering a little bit your table info... if a certain id/name doesn't exist on a certain day , it won't appear on the table.

    select
    distinct b.id, b.name, b.day1,
    case
    when t.id is not null then 1
    else 0
    end as day2
    from
    (
    select
    distinct a.id, a.name,
    case
    when t.id is not null then 1
    else 0
    end as day1
    from
    (
    select
    distinct id, name
    from
    table t (nolock)
    ) as A
    left join
    table t (nolock)
    on a.id=t.id and t.date='xxxxx' /*Date 1*/
    ) as B
    left join
    table t (nolock)
    on b.id=t.id and t.date='xxxxx' /*date 2*/

    and another try, for your actual table structure:

    select
    distinct a.id, a.name,
    t.yesno day1,
    t.yesno day2, ... dayn
    from
    (
    select
    distinct id, name
    from
    table t (nolock)
    ) as A
    inner join
    table t (nolock)
    on a.id=t.id and t.date='xxxxx' /*Date 1*/
    inner join
    table t2 (nolock)
    on a.id=t2.id and t2.date='xxxxx' /*Date 2*/
    ....
    inner join
    table tN
    on a.id=tn.id and tn.date='xxxxx' /*Date N*/


    hope this helps