Help with Group By query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Group By query

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2007-06-06 : 13:31:07

This is a fairly compliacted query and I just can't seem to figure it out.
I will try to psuedocode it to see if anyone can figure out how to do it with sql.

Table Descriptions:
I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house.
Houses: HouseID, HouseDescription
Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),

What I need:
I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").

A query would be greatly appreciated.

Thanks,

DB

If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.

Problems: problem_id, problem_inspection
house_problems: problem_id, houseID

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-06-06 : 13:59:54

1. Join the two tables using OUTER join to include the houses which are not yet inspected. Outer joins include rows that do not have a match.

2. The join should include a date check with maximum date for the particluar house. You can use a correlated sub query.

Look into BOL for OUTER Join and Correlated queries.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-07 : 00:08:42

1. Create a derived table which returns the house id and max(inspection_date) by grouping the inspection table for house_id
2 .Uoter join(left) house table with the above derived table on house.house_id = <derived able>.house_id
something like this

Select a.house_id, b.inspection_date, Case when b.inspection_date is null then 'Not inspected'
else 'Inspected' end from house a left join
(Select house_id, max(inspection_date) from
inspection group by house_id ) as b
on a.house_id = b.house_id

--------------------------------------------------
S.Ahamed

dbenoit64
Starting Member

36 Posts

Posted - 2007-06-11 : 13:59:19

Select a.house_id, b.inspection_date, Case when b.inspection_date is null then 'Not inspected'
else 'Inspected' end from house a left join
(Select house_id, max(inspection_date) from
inspection group by house_id ) as b
on a.house_id = b.house_id

kind of works but the problem is that I need another column from inspections (pass_inspection) so a group by screws that up...

Is there a way I could get an example of what you're talking about cvraghu?

Thanks

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-12 : 00:24:45

add another column in the select list as

Coalesce((Select pass_inspection from inspection where house_id = a.house_id and inspection_date = b.inspection_date), 'No') as [Pass Insepection]

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources