return 1 record from inner joins

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)

return 1 record from inner joins

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2008-09-16 : 06:32:36

I have 3 tables that are joined like the following

SELECT departments.*, risk_hazards.*, risks.*"
FROM (departments INNER JOIN risks ON departments.dept_id = risks.deptid) INNER JOIN risk_hazards ON risks.risk_id = risk_hazards.riskid
where deptid=7

Now, the risk_hazards table can have multiple entries per entry in the risks table (tied to the risks table by the riskid field).

My problem is that I want to return all the records in the risks table but only one from the risk_hazards table each time. At the moment it returns everything from risk_hazards associated with a record in the risks table.

The departments table is only tied to risks table by the deptid field.

Sorry for the crap explanation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 06:38:51

GROUP BY riskid in risk_hazards table and take aggregate on other fields like MIN(),MAX()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 06:42:14

something like

SELECT departments.*, tmp.*, risks.*"
FROM departments 
INNER JOIN risks 
ON departments.dept_id = risks.deptid 
INNER JOIN 
(SELECT riskid,MAX(field1) AS field1,MAX(field2) AS field2...
FROM risk_hazards 
GROUP BY riskid) tmp
ON risks.risk_id = tmp.riskid
where deptid=7

bpsintl
Posting Yak Master

132 Posts

Posted - 2008-09-16 : 08:23:38

You are a star, spot on!

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 08:24:37

quote:
Originally posted by bpsintl

You are a star, spot on!

Cheers

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources