Querying JOINED Tables - 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

SQL Server Development (2000)

Querying JOINED Tables

Author

Topic

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 14:02:51

Hey Gang,

I am having some trouble displaying
ALL the names and each persons MAX(Lbs).

The thing is the NAMES and the Lbs are in seperate tables.

I have a query but, it will display the name and Max(Lbs) of only the individual with the MAX (LBS) in the entire table .....only one row is displayed.

Here is the query I have:
Select F.FishId, F.FirstName, F.LastName,F.Sponsor,Fish.Lbs,Fish.Oz From Fish Fish, FishAngler F Where Fish.FishId = F.FishId and Fish.Lbs =(Select Max(Lbs) from Fish F)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 14:10:07

[code]
Select F.FishId, F.FirstName, F.LastName, F.Sponsor, Fish.Lbs
From FishAngler F
join (select FishId, max(Lbs) as Lbs from Fish group by FishId) Fish on Fish.FishId = F.FishId
[/code]

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-06 : 14:13:07

If you don't know how to do this simple query, you need to learn SQL.

Try these links:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

CODO ERGO SUM

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 14:28:02

Thanks for your help guys,

SPIRIT.....You have brought me much closer to resolving this problem....now when I try adding another field to the SELECT statement in the QUERY ANALYZER I get this error:
"Invalid column name 'Oz'."

The column name "Oz" is in the Fish Table....the Lbs field is in the same table as the Oz and the Lbs works fine.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 14:49:09

here you go:


Select F.FishId, F.FirstName, F.LastName, F.Sponsor, Fish1.Lbs, Fish1.Oz
From FishAngler F
     join (select FishId, max ( Lbs ) as Lbs from Fish group by FishId) Fish on Fish.FishId = F.FishId
	 join Fish Fish1 on Fish.FishId = Fish1.FishId and Fish.Lbs = Fish1.Lbs

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 14:52:26

GREAT STUFF!!!.....You got my message SPIRIT. Thank You Sir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources