Select from multiple tables on specific criteria

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

Select from multiple tables on specific criteria

Author

Topic

Redser
Starting Member

9 Posts

Posted - 2011-11-04 : 16:08:59

I have two tables, Applications & Decisions. Each Application can have many Decisions recorded against it on different dates.

I need to identify all Applications were the most recent Decision was before a specifit date e.g. 01/01/11. Any help would be appreciated.

singularity
Posting Yak Master

153 Posts

Posted - 2011-11-04 : 20:37:04

[code]
select application
from applications a
join decisions b on a.application_id = b.application_id
group by application
having max(decision_date) < '01/01/11'
[/code]

Redser
Starting Member

9 Posts

Posted - 2011-11-05 : 07:27:17

Singularity
Thanks for your help it's worked perfectly.

Can I be cheeky and ask for a bit more assistance. Is there a way of bringing back additional rows from my Application table? My understanding is that you have to include all fields from a select statement in the Group By. If I was to do that then the query won’t bring back the correct information.

singularity
Posting Yak Master

153 Posts

Posted - 2011-11-05 : 14:25:22

Several ways to do this, here's one example:


select *
from applications a
where a.application in 
(select application
from applications a
join decisions b on a.application_id = b.application_id
group by application
having max(decision_date) < '01/01/11')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-06 : 02:44:37

quote:
Originally posted by Redser

Singularity
Thanks for your help it's worked perfectly.

Can I be cheeky and ask for a bit more assistance. Is there a way of bringing back additional rows from my Application table? My understanding is that you have to include all fields from a select statement in the Group By. If I was to do that then the query won’t bring back the correct information.


select a.*
from applications a
join (select application_id 
      from decisions 
      group by application_id
      having max(decision_date) < @yourdate)b 
on a.application_id = b.application_id

you can declare a parameter as @yourdate and pass any date value through it

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

Redser
Starting Member

9 Posts

Posted - 2011-11-07 : 16:00:12

Singularity & Visakh16

This was my first post and would like to say thanks for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 23:51:17

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources