July 22, 2003 at 8:03 am
I'm having problems writing a query. I'm trying to join two data sets, but the restrictions of the where clause are limiting lone values. eg
Year ID SubID Value
1990 1 1 12
1991 1 1 15
1992 1 1 27
------------
1989 1 2 13
1990 1 2 23
1991 1 2 21
-----------
1990 1 3 12
1991 1 3 14
I want to get the values or SubID's 1 and 2 in the form of ...
Year ValuesOfSubID1 ValuesOfSubID2
1989 NULL 13
1990 12 23
1991 15 21
1992 27 NULL
However due to the where restriction to eliminate SubID equalling 3 (or other numbers), I can only get the common values. 1990 and 1991. My query looks something like this....
Select a.year, a.value as 'ValueOfSubID1', b.value as 'ValueOfSubID2' from MyTable as a
Full Outer Join MyTable as b
On a.Year = b.Year and a.ID = b.ID
where a.ID = 1
a.SubID = 1 and b.SubId = 2
Order by Year
As stated, this query only returns common values. There are an unknown value/year sets with different SubIDs and I will also need to have more than two sets joined at a time eg
Year ValueOfID1 ValueOfID2 ValueOfID3 ...
Can anyone help with this?
Thanks.
July 22, 2003 at 8:28 am
My table values got 'squished'. Here they are again, hopefully more readable. (I'll comma delimiter just in case)
Year, ID, SubID, Value
1990, 1, 1, 12
1991, 1, 1, 15
1992, 1, 1, 27
------------
1989, 1, 2, 13
1990, 1, 2, 23
1991, 1, 2, 21
-----------
1990, 1, 3, 12
1991, 1, 3, 14
Year, ValuesOfSubID1, ValuesOfSubID2
1989, NULL, 13
1990, 12, 23
1991, 15, 21
1992, 27, NULL
July 22, 2003 at 9:50 am
Hmmmm....this one is tougher than it looks. I was trying to use derived tables, but it gets ugly after you start needing more than two column sets. Ya might need a guru on this one. Anyone else have any ideas?
July 22, 2003 at 11:40 am
This query should return what you need:
Select Coalesce(a.year,b.year) as [Year], a.value as 'ValueOfSubID1', b.value as 'ValueOfSubID2' from
(Select * From MyTable WHERE ID = 1 and SubID = 1) a
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 2) b
On a.Year = b.Year and a.ID = b.ID
July 22, 2003 at 12:18 pm
That is exactly what I was doing except I used an ISNULL in place of the Coalesce ,which I never knew existed. Thank you! Where I got lost was trying to perform a third self join for another column set. How can this be done?
July 22, 2003 at 12:29 pm
If I understand what you want... just keep expanding the query:
Select Coalesce(a.year,b.year,c.year) as [Year], a.value as 'ValueOfSubID1',
b.value as 'ValueOfSubID2', c.value as 'ValueOfSubID3'
FROM
(Select * From MyTable WHERE ID = 1 and SubID = 1) a
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 2) b
On a.Year = b.Year and a.ID = b.ID
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 3) c
On a.Year = c.Year and a.ID = c.ID
From your sample data, this returns:
Year ValueOfSubID1 ValueOfSubID2 ValueOfSubID3
----------- ------------- ------------- -------------
1989 NULL 13 NULL
1990 12 23 12
1991 15 21 14
1992 27 NULL NULL
(4 row(s) affected)
July 22, 2003 at 1:08 pm
Yes that does work in that case. But try this add the row ----
(1992 1 3 23)
to Varcy's data.
Then the query....
Select Coalesce(a.ryear,b.ryear,c.ryear) as rYear, a.rvalue as 'ValueOfSubID1',
b.rvalue as 'ValueOfSubID2', c.rvalue as 'ValueOfSubID3'
FROM
(Select * From MyTable WHERE ID = 1 and SubID = 2) a
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 1) b
On a.rYear = b.rYear and a.ID = b.ID
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 3) c
On a.rYear = c.rYear and a.ID = c.ID
order by a.ryear
Notice: I'm pulling subId = 2 before subid = 1
There are two results for 1992
This could be fixed by using
[On b.rYear = c.rYear and b.ID = c.ID]
instead of a, but that's only because I knew the data was set up this way.
July 22, 2003 at 1:22 pm
I think I got it...
Select Coalesce(a.ryear,b.ryear,c.ryear) as rYear, a.rvalue as 'ValueOfSubID1',
b.rvalue as 'ValueOfSubID2', c.rvalue as 'ValueOfSubID3'
FROM
(Select * From MyTable WHERE ID = 1 and SubID = 2) a
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 1) b
On a.rYear = b.rYear and a.ID = b.ID
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 3) c
On Coalesce(a.ryear,b.ryear) = c.rYear and Coalesce(a.id,b.id) = c.ID
order by a.ryear
By also using the coalesce in the On statements, you eliminate the posibility of having NULL values for both a.id and a.ryear and therefor getting dup records. I'll do some more testing to make sure. If there are more column sets added, the coalesce in the On statedment will also have to be expanded to cover them. Brendthess, I love the Coalesce call. It is something that could have saved me many heaaches in the past. Thanks.
July 22, 2003 at 1:51 pm
Actually, there is an easier way:
Select Coalesce(a.year,b.year,c.year) as [Year], a.value as 'ValueOfSubID1',
b.value as 'ValueOfSubID2', c.value as 'ValueOfSubID3'
FROM
(Select * From MyTable WHERE ID = 1 and SubID = 2) a
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 1) b
On a.Year = b.Year
FULL OUTER JOIN
(Select * From MyTable WHERE ID = 1 and SubID = 3) c
On a.Year = c.Year or b.year = c.year
Order By Coalesce(a.year,b.year,c.year)
The OR statement in the join is the key.
Glad you like Coalesce. This is a really useful, and fairly unknown, statement.
July 22, 2003 at 2:31 pm
Ahhh...nice. I guess the ids=ids wouldn't be needed since they are already determined. Dunno though, I'm likeing the 'Coalesce'. 😉 Good call!
July 22, 2003 at 2:55 pm
Jeez! I didn't know I would get so much dedication on this problem. Thank you both. I'd never used Coalesce before. Anyway, this query works great. I appreciate the effort in solving this!
July 23, 2003 at 5:47 am
declare @t table ([Year] int, [ID] int, SubID int , Value int)
insert @t
select 1990, 1, 1, 12 union all
select 1991, 1, 1, 15 union all
select 1992, 1, 1, 27 union all
select 1989, 1, 2, 13 union all
select 1990, 1, 2, 23 union all
select 1991, 1, 2, 21 union all
select 1990, 1, 3, 12 union all
select 1991, 1, 3, 14
--select * from @t
-----------------------
select [Year],
ValuesOfSubID1=max(case SubID when 1 then Value end),
ValuesOfSubID2=max(case SubID when 2 then Value end),
ValuesOfSubID3=max(case SubID when 2 then Value end)
from @t
group by [Year]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply