need help with simple, yet complex query

[pandatime]

Ok, let's see if I can explain this. The query below returns the result set below:

select
a.group,
a.id,
a.title,
c.completed_dt,
c.viewed_dt
from myTableA a
left join myTableC c on (a.id = c.id)
and c.id = 123
order by c.completed_dt desc, newid() -- randomizes

Sorry about the formatting:

group id title completed_dt viewed_dt
GROUP1 1000 BLUE 2010-09-01 2010-09-02
NULL 1003 title1 NULL NULL
GROUP1 1002 BLUEST NULL NULL
NULL 1006 title4 NULL NULL
GROUP1 1001 BLUER NULL NULL

Basically, what I want is, where "group" is NOT null, e.g. "GROUP1", then return the row from the group with the MIN(id) where completed_dt is null. I DO want rows where completed_dt is not null in all cases. So that wouldn't be part of what I'm trying to eliminate.

So the result set I want is actually:

group id title completed_dt viewed_dt
GROUP1 1000 BLUE 2010-09-01 2010-09-02
NULL 1003 title1 NULL NULL
NULL 1006 title4 NULL NULL
GROUP1 1001 BLUER NULL NULL

That is, I don't want "BLUEST". I only want BLUE and BLUER (BLUE because completed_dt is not null, and BLUER because it's the min (id) where completed_dt is null from GROUP1.

The problem is, I don't know how to write this query. And all rows without completed_dt must be randomized.

Any suggestions greatly appreciated!!

Thanks

 

[markros]

Try in SQL Server 2005 and up

-- First all completed - inner join
;with cteCompleted as (select
a.group,
a.id,
a.title,
c.completed_dt,
c.viewed_dt
from myTableA a
inner join myTableC c on (a.id = c.id)
and c.id = 123),
-- then all non-completed - LEFT JOIN with IS NULL
cteMin as (select
a.group,
a.id,
a.title,
c.completed_dt,
c.viewed_dt, row_number() over (partition by a.Group order by a.ID) as Row
from myTableA a
LEFT join myTableC c on (a.id = c.id)
and c.id = 123 where c.ID IS NULL)
-- Now get a sum of both
select * from (select * from cteCompleted
union all
select Group, ID, Title, Completed_ID, Viewed_dt
from cteMin where Row = 1) u order by Completed_dt, NewID() -- to randomize

PluralSight Learning Library

 

[pandatime]

Sadly, it's not working due to the fact that TableC can contain id's for various people.

So, let's say Joe has id 1000, but Mary has id 1005, then 1005 doesn't show up in Joe's output. That's what I'm seeing.

I'm extremely confused at this point :-(

 

[markros]

I'm not sure why it's not working. The logic seems correct to me based on what you posted in the 1st message.

Can you post the creation statements for both tables and insert statements and the desired output.

PluralSight Learning Library

 

[pandatime]

Hi,

Starting completely over from scratch, here's some sample data:

TableA (i.e. available)
group id title level
GROUP1 1000 BLUE 1
GROUP1 1002 BLUER 2
GROUP1 1003 BLUEST 3
NULL 1004 CATS NULL
NULL 1005 DOGS NULL
NULL 1006 MONKEYS NULL

TableC (i.e. completed)
person group id title completed_dt
Joe GROUP1 1000 BLUE 2010-09-01
Joe NULL 1006 MONKEYS 2010-09-02

What the output for JOE need to be is:

group id title completed_dt
GROUP1 1000 BLUE 2010-09-01
NULL 1006 MONKEYS 2010-09-02
GROUP1 1002 BLUER
NULL 1004 CATS
NULL 1005 DOGS

That is, everything in TableC for Joe PLUS everything in TableA (all available records), EXCEPT in cases where group is not null. In cases where group is not null, then I only want the record for MIN(level), in this case "2" for BLUER, not BLUE, because Joe hasn't "achieved" this level yet. He's only completed "BLUE" in the TableC table. So we don't want to see BLUEST as an available option, only BLUER since that's the next level in GROUP1.

Thanks much

 

[pandatime]

Hi,

Nevermind, I figured it out. Turns out if I put the results for both tables in a temp table first, it is a relatively easy matter to get what I want by the use of a correlated subquery using min.

I guess I got myself confused by overcomplicating the issue, and not taking a sufficient break to think about the matter (which often happens!)

Thanks

 

[markros]

BTW, I think the query I suggested originally will get the desired result. Or a slight variation of it.

PluralSight Learning Library