ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button
Article:
  SQL Subqueries
Subject:   A little SQL Subquery help, please?
Date:   2004-12-21 12:34:42
From:   msantoyo
Response to: A little SQL Subquery help, please?

"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