Join to retrieve row conditionally

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join to retrieve row conditionally

Author

Topic

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 08:24:06

Hi,

I have two tables Named Master and Child where id & id version is the primary key in Master Table and that combination has multiple rows in Child table

Master Table

Id | Id Version| Name
1|1|id1
1|2|id2
2|1|id2

Child Table

Id | Id Version| Status
1|1|A
1|1|B
1|2|A
2|1|B

Desired output:

Id | Id Version | Name | Status
1|1|id1|B
1|2|id1|A
2|1|id2|B

For a passed Id and Id Version, I want to retrieve the status from the table conditionally. For a particular id and id version, if we have a status other than 'A', i would like to retrieve that where as if it has only 'A' i want to retrieve that

your help will be greatly appreciated

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 08:28:36

[code]SELECT m.Id, m.IdVersion, m.Name, Status = MAX(c.Status)
FROM master m
INNER JOIN child c
ON m.Id = c.Id
AND m.IdVersion = c.IdVersion
GROUP BY m.Id, m.IdVersion, m.Name
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 10:29:59

Thanks KH. But the values of Status can be A, C, S and X. I want to get a value other than "S" if its there or "S" if it is the only value.

I weas not clear earlier and MAX approach wont work here.

Please suggest

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:40:22

[code]SELECT m.Id,m.[Id Version],
CASE WHEN c.MaxValue IS NULL AND c.Presence =1 THEN 'S'
ELSE c.MaxValue END AS Status
FROM Master m
INNER JOIN (SELECT Id,[Id Version],
MAX(CASE WHEN Status='S' THEN NULL ELSE Status END) AS MaxValue,MAX(CASE WHEN Status='S' THEN 1 ELSE 0 END) AS Presence
FROM Child
GROUP BY Id,[Id Version])c
ON m.Id=c.Id
AND m.[Id Version]=c.[Id Version][/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 11:07:50

quote:
Originally posted by ra_sasi

Thanks KH. But the values of Status can be A, C, S and X. I want to get a value other than "S" if its there or "S" if it is the only value.

I weas not clear earlier and MAX approach wont work here.

Please suggest

What is the sequence of perference ? A, C, X which one comes first ?

KH
[spoiler]Time is always against us[/spoiler]

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 11:11:13

Thnaks a lot visakh..it works wonderfully

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 11:19:57

[code]
select [Id], [Id Version], [Name], Status
from
(
select m.[Id], m.[Id Version], m.[Name], c.Status,
row_no = row_number() over (partition by m.[Id], m.[Id Version]
order by case when c.[Status] = 'S' then 1 else 0 end, c.[Status])
from Master m
inner join Child c on m.[Id] = c.[Id]
and m.[Id Version] = c.[Id Version]
) d
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 13:10:43

Thanks KH.
Visakh, in the first approach
How do I retrieve another two columns Reason Code, Source along with Status from the Child table with out having them in group by?

If i list them in select alone, SQL server is throwing an error

I am tryign to use this approach as I am using this to join with many other joins

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:16:00

quote:
Originally posted by ra_sasi

Thanks KH.
Visakh, in the first approach
How do I retrieve another two columns Reason Code, Source along with Status from the Child table with out having them in group by?

If i list them in select alone, SQL server is throwing an error

I am tryign to use this approach as I am using this to join with many other joins

how will that fields be having values? will they be same for [Id], [Id Version] combination? If not what value do you want out of them? can you show some sample data to illustrate it plzz.

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 13:31:01

Both those fields can have any values and the values are not same for particular combination. They are free text(varchar fields)

Child Table
Id | Id Version| Status |ReasonCode| SourceID
1|1|S|<Blank>|<Blank>
1|1|X|text1| 123
1|2|S|<Blank>|<Blank>
1|2|C|text2|345
2|1|S|<Blank>|<Blank>

Desired output:
Id | Id Version | Name | Status | Reason Code| SourceID
1|1|id1|X|text1| 123
1|2|id1|C|text2|345
2|1|id2|S|<Blank>|<Blank>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:34:14

quote:
Originally posted by ra_sasi

Both those fields can have any values and the values are not same for particular combination. They are free text(varchar fields)

Child Table
Id | Id Version| Status |ReasonCode| SourceID
1|1|S|<Blank>|<Blank>
1|1|X|text1| 123
1|2|S|<Blank>|<Blank>
1|2|C|text2|345
2|1|S|<Blank>|<Blank>

Desired output:
Id | Id Version | Name | Status | Reason Code| SourceID
1|1|id1|X|text1| 123
1|2|id1|C|text2|345
2|1|id2|S|<Blank>|<Blank>

so as before what value do you want to retrive out of them? The one associated to retrieved Status?

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 13:36:47

Yes..thats what I am looking for

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:52:11

[code]SELECT m.Id,m.[Id Version],[c2.Reason Code],c2.SourceID,
CASE WHEN c.MaxValue IS NULL AND c.Presence =1 THEN 'S'
ELSE c.MaxValue END AS Status
FROM Master m
INNER JOIN (SELECT Id,[Id Version],
MAX(CASE WHEN Status='S' THEN NULL ELSE Status END) AS MaxValue,MAX(CASE WHEN Status='S' THEN 1 ELSE 0 END) AS Presence
FROM Child
GROUP BY Id,[Id Version])c
ON m.Id=c.Id
AND m.[Id Version]=c.[Id Version]
INNER JOIN Child c2
ON m.Id=c2.Id
AND m.[Id Version]=c2.[Id Version]
AND CASE WHEN c.MaxValue IS NULL AND c.Presence =1 THEN 'S'
ELSE c.MaxValue END=c2.Status[/code]

ra_sasi
Starting Member

9 Posts

Posted - 2008-08-27 : 14:04:03

Thanks Visakh.it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources