Basic Join Issue , please help!!!

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)

Basic Join Issue , please help!!!

Author

Topic

ozonic
Starting Member

10 Posts

Posted - 2002-04-25 : 07:45:16

Hi.

I have the following scenario.

clients clientitems
____________________________

clientname clientname | itemnumber
jeremy jeremy 1
jeremy 2
dave dave 1
dave 2
dave 3

alright so in reality I am not linking by client name but for illusration purposes a 1 to many link exists between the clients and clientitems tables.

So what I would like to do is join the tables. Except I would only want 1 record from clientitems being returned. This would be the greatest item number. Or if you like max(itemnumber).

Can you assist oh Expert.

dsdeming

479 Posts

Posted - 2002-04-25 : 07:56:02

Try something like

SELECT c.ClientName, MAX( i.ItemNumber )
FROM Clients c JOIN ClientItems i ON c.ClientName = i.ClientName
GROUP BY c.ClientName

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-04-25 : 07:59:58

1. select clients.clientname,
MaxOfItems = (SELECT max(itemnumber) from clientitems where clientitems.clientname = clients.clientname)
from clients

OR

2. select clients.clientname,
max(clientitems.itemnumber)
from clients left join clientitems on clients.clientname = clientitems.clientname
group by clients.clientname

ozonic
Starting Member

10 Posts

Posted - 2002-04-25 : 08:14:52

SELECT TOP 100 PERCENT AA.client_mastid AS Expr1, AA.User_ID, AA.eppaccount, AA.name AS client_name, BB.LastName, BB.FirstName AS Expr7, BB.Cell,
BB.Tel_Number, BB.Email AS Expr10, BB.Tel_Code AS Expr11, CC.clientid, CC.CommentDate, CC.Comment, MAX(CC.Logid) AS logid
FROM epp_client.dbo.client_mast AA INNER JOIN
dbo.Webuser_mast BB ON AA.User_ID = BB.User_id LEFT OUTER JOIN
epp_client.dbo.visitlog CC ON AA.client_mastid = CC.clientid
GROUP BY AA.client_mastid, CC.Logid, CC.Comment, AA.User_ID, AA.eppaccount, AA.name, BB.LastName, BB.FirstName, BB.Cell, BB.Tel_Number, BB.Email,
BB.Tel_Code, CC.clientid, CC.CommentDate
ORDER BY AA.client_mastid, CC.CommentDate DESC

this is my sql so please tell me why I get back duplicates on clientid

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-25 : 08:30:55

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15023

ozonic
Starting Member

10 Posts

Posted - 2002-04-26 : 03:11:24

YELLOWBUG , you are a genuis !!!!. You are really clever. It worked, Just like you said it would.

1. select clients.clientname,
MaxOfItems = (SELECT max(itemnumber) from clientitems where clientitems.clientname = clients.clientname)
from clients

So far this is the only thing that has worked. And I've tried all variations of the second option.

Thanks
!!!!!

ozonic
Starting Member

10 Posts

Posted - 2002-04-26 : 03:17:45

SELECT AA.name, AA.client_mastid, AA.eppaccount,
(SELECT MAX(logid)
FROM epp_client.dbo.visitlog
WHERE clientid = AA.client_mastid) AS Maxlogid, BB.Comment, BB.CommentDate
FROM epp_client.dbo.client_mast AA LEFT OUTER JOIN
epp_client.dbo.visitlog BB ON BB.Logid =
(SELECT MAX(logid)
FROM epp_client.dbo.visitlog
WHERE clientid = AA.client_mastid)

The final SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources