Women in Technology

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"

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