hard join between 3 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 2005 Forums

Transact-SQL (2005)

hard join between 3 tables

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2014-06-19 : 08:28:45

Hallo all.

I have the following problem.
I have 3 tables GOOD, SELL, CUSTOMER.SUPPLIER and I need select from SELL.

GOOD
id.supplier....item
111............444
222............777
333............999

SELL
id.customer..........item
111..................999
222..................444
333..................777

CUSTOMER.SUPPLIER
id................name
111...............SHELL
222...............APPLE
333...............DELL

I need this select from SELL:

id.....name.customer....name.supplier....item
111......SHELL............DELL............999
222......APPLE............SHELL...........444
333......DELL..............APPLE..........777

look first row from result:

"name.customer" for ID 111 is SHELL
supplier item 999 is by GOOD table ID 333 and ID 333=DELL "name.supplier" is DELL

but I dont select from this 3 tables..It is possible do select and join?

thanks all.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 08:49:56

[code]
;with GOOD
AS
(select 111 as idSupplier, 444 as item union all
select 222,777 union all
select 333,999)

,SELL
AS
(select 111 as idCustomer ,999 as item union all
select 222,444 union all
select 333,777)

,CUSTOMER_SUPPLIER
AS
(select 111 as id,'SHELL' as name union all
select 222,'APPLE' union all
select 333,'DELL')

SELECT
CS.id AS [ID]
,CS.Name AS [name.Customer]
,SUP.name AS [name.Supplier]
,SELL.item AS [item]
FROM
CUSTOMER_SUPPLIER AS CS
INNER JOIN SELL AS SELL
ON CS.id = SELL.idCustomer
INNER JOIN GOOD AS G
ON SELL.item=G.item
INNER JOIN CUSTOMER_SUPPLIER AS SUP
ON G.idSupplier=SUP.id
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 08:50:18

and the output:


ID	name.Customer	name.Supplier	item
111	SHELL	DELL	999
222	APPLE	SHELL	444
333	DELL	APPLE	777

sabinWeb MCP

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-06-19 : 08:52:28

Try this:

SELECT S.ID, S.NAME AS CUSTOMER, SU2.NAME AS SUPPLIER, S.ITEM
FROM SELL S
INNER JOIN SUPPLIER SU ON S.ID=SU.ID
INNER JOIN GOOD G ON G.ITEM=S.ITEM
INNER JOIN SUPPLIER SU2 ON SU2.ID=G.ID

Edit: Sorry stepson, missed your post

--------------------
Rock n Roll with SQL

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 09:05:08

quote:
Edit: Sorry stepson, missed your post

No prob, important is that , we hope , that this post is solved

sabinWeb MCP

Gekko
Yak Posting Veteran

63 Posts

Posted - 2014-06-19 : 09:10:31

thanks boys ok best regards

finally it was easy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources