Problem with Joins - 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)

Problem with Joins

Author

Topic

Faiyth
Starting Member

19 Posts

Posted - 2008-03-21 : 19:05:38

Alright here's what I'm trying to do. I've been searching all day for the solution and can't seem to figure it out...

I have 3 tables. Table1 has all the Restaurants in it, Table2 has all the Inspection Scores for those Restaurants, Table3 has all the Inspection Details for those Restaurants.

TABLE1
-----------
Estab ID - Estab Name - Estab City
1 - Coffee Palace - Portland
2 - Coffee Fort - Tanland
3 - Hotdogs Galore - Celeven

TABLE2
------------------
Insp ID - Estab ID - Insp Date - Insp Score - Insp Type
1 - 3 - 03/01/08 - 100 - F
2 - 3 - 02/01/08 - 90 - A
3 - 2 - 03/21/08 - 86 - F
4 - 1 - 02/11/08 - 71 - C

TABLE3
-------------
Inspection Type - Inspection Type Desc
F - Full
A - Partial
C - Closed

Because restaurant 3 has been inspected more then once (it's been inspected twice) In my results I'm getting that restaurant twice.

What I want is all the records in Table1 and then the newest score and matching Inspection Type. I don't want duplicates of the information in Table1.

Here's my code so far:

SELECT
t1.[Estab ID] eid,
[Estab Name] as ename,
[Estab City] as ecity,
[Insp Date] as idate,
[Insp Score] as iscore,
[Inspection Type Desc] as itype
FROM Table1 t1, Table2 t2, Table3 t3 WHERE t1.[Estab ID]=t2.[Estab ID] and t2.[Inspection Type]=t3.[Inspection Type]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 19:36:53

Use a GROUP BY and the MAX aggregate function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-23 : 12:41:07

[code]SELECT t1.[Estab ID],
t1.[Estab Name],
t1.[Estab City],
t2.[Insp Date],
t2.[Insp Score],
t3.[Inspection Type Desc]
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t2.[Estab ID]=t1.[Estab ID]
INNER JOIN (SELECT [Estab ID],MAX([Insp Date]) AS MaxInspDate
FROM TABLE2
GROUP BY [Estab ID])tmp
ON tmp.[Estab ID]=t2.[Estab ID]
AND tmp.MaxInspDate=t2.[Insp Date]
INNER JOIN TABLE3 t3
ON t3.[Inspection Type]=t2.[Insp Type][/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources