Question on Second Join - 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

General SQL Server Forums

New to SQL Server Programming

Question on Second Join

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-02 : 02:29:32

[code]I'm doing an unusual sort of inner join where the joined column can match more than one row,
but I don't really care - I just want the first row it finds, and that works. What I'm having a problem
conceptualizing is doing a second join within that. I will illustrate with an example. I have 3 tables:

TEST_ITEMS:

--ItemID--
AAAAAAAAAA
BBBBBBBBBB
CCCCCCCCCC

VENDOR_ITEM:

--ItemID--|VendorID
AAAAAAAAAA|111111--
AAAAAAAAAA|222222--
BBBBBBBBBB|333333--

VENDOR_NAME:

VendorID|VendorName---------
111111--|Name for vendor One
222222--|Name for vendor two
333333--|Name for vendor 3

I have this query:

SELECT TEST_ITEMS.ItemID
,(SELECT TOP 1 VendorID FROM VENDOR_ITEM WHERE VENDOR_ITEM.ItemID = TEST_ITEMS.ItemID) As VendorID
FROM TEST_ITEMS

It yields exactly the results I want:

--ItemID---VendorID---
AAAAAAAAAA|111111
BBBBBBBBBB|333333
CCCCCCCCCC|NULL--

But I want to modify my query to yield these results:

--ItemID--|VendorID|VendorName---------
AAAAAAAAAA|111111--|Name for vendor One
BBBBBBBBBB|333333--|Name for vendor 3--
CCCCCCCCCC|NULL----|NULL---------------

I've tried a few things that haven't worked. Here is a script to create the test data:

USE [Northwind]
GO
/****** Object: Table [dbo].[TEST_ITEMS] Script Date: 06/01/2012 16:32:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST_ITEMS](
[ItemID] [varchar](15) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TEST_ITEMS] ([ItemID]) VALUES (N'AAAAAAAAAA')
INSERT [dbo].[TEST_ITEMS] ([ItemID]) VALUES (N'BBBBBBBBBB')
INSERT [dbo].[TEST_ITEMS] ([ItemID]) VALUES (N'CCCCCCCCCC')
/****** Object: Table [dbo].[VENDOR_ITEM] Script Date: 06/01/2012 16:32:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VENDOR_ITEM](
[ItemID] [varchar](15) NULL,
[VendorID] [varchar](8) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[VENDOR_ITEM] ([ItemID], [VendorID]) VALUES (N'AAAAAAAAAA', N'111111')
INSERT [dbo].[VENDOR_ITEM] ([ItemID], [VendorID]) VALUES (N'AAAAAAAAAA', N'222222')
INSERT [dbo].[VENDOR_ITEM] ([ItemID], [VendorID]) VALUES (N'BBBBBBBBBB', N'333333')
/****** Object: Table [dbo].[VENDOR_NAME] Script Date: 06/01/2012 16:32:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VENDOR_NAME](
[VendorID] [varchar](8) NULL,
[VendorName] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[VENDOR_NAME] ([VendorID], [VendorName]) VALUES (N'111111', N'Name for vendor One')
INSERT [dbo].[VENDOR_NAME] ([VendorID], [VendorName]) VALUES (N'222222', N'Name for vendor two')
INSERT [dbo].[VENDOR_NAME] ([VendorID], [VendorName]) VALUES (N'333333', N'Name for vendor 3')
[/code]

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-02 : 06:14:56

[code];with cte as
(

select ROW_NUMBER() over (PARTITION by t1.itemid order by (select null)) rn,
t1.ItemID,t2.VendorID,t3.VendorName from TEST_ITEMS t1 left join VENDOR_ITEM t2 on t1.ItemID =t2.ItemID
left join [VENDOR_NAME] t3 on t2.VendorID=t3.VendorID)
select * from cte where rn = 1[/code]

Vijay is here to learn something from you guys.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-02 : 06:21:58

You can do something like this:


--Creating Tables

CREATE TABLE [dbo].[TEST_ITEMS](
	[ItemID] [varchar](15) NULL
)

CREATE TABLE [dbo].[VENDOR_ITEM](
	[ItemID] [varchar](15) NULL,
	[VendorID] [varchar](8) NULL
)

CREATE TABLE [dbo].[VENDOR_NAME](
	[VendorID] [varchar](8) NULL,
	[VendorName] [varchar](30) NULL
)


--Inserting Sample Data

INSERT [dbo].[TEST_ITEMS] ([ItemID]) VALUES (N'AAAAAAAAAA')
INSERT [dbo].[TEST_ITEMS] ([ItemID]) VALUES (N'BBBBBBBBBB')
INSERT [dbo].[TEST_ITEMS] ([ItemID]) VALUES (N'CCCCCCCCCC')

INSERT [dbo].[VENDOR_ITEM] ([ItemID], [VendorID]) VALUES (N'AAAAAAAAAA', N'111111')
INSERT [dbo].[VENDOR_ITEM] ([ItemID], [VendorID]) VALUES (N'AAAAAAAAAA', N'222222')
INSERT [dbo].[VENDOR_ITEM] ([ItemID], [VendorID]) VALUES (N'BBBBBBBBBB', N'333333')

INSERT [dbo].[VENDOR_NAME] ([VendorID], [VendorName]) VALUES (N'111111', N'Name for vendor One')
INSERT [dbo].[VENDOR_NAME] ([VendorID], [VendorName]) VALUES (N'222222', N'Name for vendor two')
INSERT [dbo].[VENDOR_NAME] ([VendorID], [VendorName]) VALUES (N'333333', N'Name for vendor 3')


--Query For Your Requirement

Select x.ItemID, x.VendorID, x.VendorName From
	(Select a.ItemID, b.VendorID, c.VendorName, ROW_NUMBER() Over (Partition By a.ItemId Order By a.ItemId) As rn From TEST_ITEMS As a
	 Left JOIN VENDOR_ITEM As b on a.ItemID = b.ItemID
	 Left JOIN VENDOR_NAME As c On b.VendorID = c.VendorID) As x
Where x.rn = 1

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 14:09:39

[code]
SELECT t.ItemID,
v.VendorID,
v.VendorName
FROM TEST_ITEMS t
LEFT JOIN (SELECT vi.ItemID,vn.VendorID,vn.VendorName
FROM VENDOR_NAME vn
INNER JOIN (SELECT ItemID,MIN(VendorID) AS VendorID
FROM VENDOR_ITEM
GROUP BY ItemID) vi
ON vi.VendorID = vn.VendorID
)v
ON v.ItemID = t.ItemID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-02 : 16:08:13

Thanks for both of your great solutions - they both worked, and I'll need to decide which one to use.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 20:02:01

quote:
Originally posted by BobRoberts

Thanks for both of your great solutions - they both worked, and I'll need to decide which one to use.

check the execution plan and use most efficient suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources