inner Join tree souce ( design pattern)

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

inner Join tree souce ( design pattern)

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-08-28 : 12:29:46

I have 5 tables - I have tried to abstract my tables into a pattern that might be recognized , the select must work on the

[ Orders ] -> [ ProductItems ] - key - [ Productdata]
[ Orders ] -> [ Customers ] - key - [ Customerdata ]

I can get one line of inner joins like this:
FROM Orders INNER JOIN ProductItem ON
Orders.Customer = SUBSTRING(ProductItems.CustomerJO, 1, 5) INNER JOIN ProductItems.PI_forKEY = ProductData.PI_KEY

BUT I need to create an inner join to both tables - ProductItems & Customers then retreive also the specific product & customer data { another inner join }

FROM Orders INNER JOIN ProductItems
ON Orders.Customer = SUBSTRING(ProductItems.CustomerJO, 1, 5)

and Also Orders INNER JOIN Customers
ON Orders.Customer = SUBSTRING(Customers.CustomerJO, 1, 5)

where Orders.Quantity > something.

How can I create a query that will get all of this ? 2 tempTables that are inner joined on CustomerJO ?

Thanks !

andrewcw

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-28 : 14:23:31

something like this...

SELECT a.field1,
b.field2,
c.field3
FROM Orders a INNER JOIN ProductItems b
ON a.Customer = SUBSTRING(b.CustomerJO, 1, 5) 
INNER JOIN Customers c
ON a.Customer = SUBSTRING(c.CustomerJO, 1, 5)
where a.Quantity > something

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-08-28 : 20:33:57

I added INNER JOINS to the query for the additional data. Then the query rteurned 10000X times the rows I expected. I created small tables to see if I could see how to fix it. Now I realize its critical to specify both keys ....as shown...

SELECT a.Customer, b.ProdName, c.something, e.city
FROM Orders AS a INNER JOIN
ProductItems AS b ON a.Customer = SUBSTRING(b.CustomerJO, 1, 5) INNER JOIN
Customers AS c ON a.Customer = SUBSTRING(c.CustomerJO, 1, 5) INNER JOIN
ProductData AS d ON b.Prod_Fkey = d.Prod_key INNER JOIN
CustomerData AS e ON c.Cust_FKEY = e.Cust_KEY
WHERE (c.CustomerJO = 'JAMES_123') and b.Prod_Fkey =someNumber and c.Cust_FKEY=someotherNumber

Thanks Andrew

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources