union and sort - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

union and sort

Author

Topic

asdf1324
Starting Member

1 Post

Posted - 2011-10-13 : 21:57:13

Need assistance with query:

have two related tables:
Submissions:† id, type, lab
Experiments:† id, name, status, subId

The 'id' and 'subId' fields are numeric, other fields are text.
The 'id' fields are unique in each table.
Submission may have multiple experiments associated with it.
The ‘subId’ field in the Experiments table stores the ‘id’ from the related record in the Submissions table.

I want, in alphabetical order, labs that have at least one experiment for which the status isn't "released" or "failed"?

I tried:
SELECT * FROM Experiments, Submissions WHERE Status <> "released","failed" ORDER BY lab ASC as subId FROM id

but this did not work. please suggest better query.
thank you,

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-14 : 01:23:01

[code]
select s.lab from submissions inner join experiments e
on s.id=e.subid
where s.status not in ('released','failed')
order by s.lab asc
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 01:43:59

[code]
select s.lab from submissions
inner join (select subid from experiments
group by subid
having count(case when s.status in ('released','failed') then null else 1 end)>0
)e
on s.id=e.subid
order by s.lab
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources