Help with Joing (what else? :)

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)

Help with Joing (what else? :)

Author

Topic

consultant
Starting Member

1 Post

Posted - 2009-03-10 : 18:00:40

Still grappling with join.

I need to get a list of Customers (email and name) based on purchase history that requires me to read 4 tables. Here are the 4 select statements in order. In the where clause I prefixed the conditional value with Select1,2,3 just to illustrate the hierarchy.

The tables with the relevant data are:

Table: Item - Contains the Price, Supplier, and Department of an Item

Select 1: Find the qualifying items

SELECT ID FROM Item WHERE SupplierID = 11 or (Price > 250 and Item.DepartmentID <> 10) or (Item.Price > 1000 and Item.DepartmentID = 10)

Table: TransactionEntry - Contains all the items in a transaction

Select 2: Find distinct transaction numbers that has at least one of the Item ID's

SELECT Distinct(TransactionNumber) FROM TransactionEntry WHERE ItemID = Select1.ID

Table: Transaction - Contains the customer ID for the transaction

Select 3: Find Customer ID for each transaction

SELECT Distinct(CustomerID) FROM "Transaction" WHERE TransactionNumber = Select2.TransactionNumber

Table: Customer - Customer Information

Select 4: Find E-mail and Name of Customer

SELECT EmailAdress,FirstName,LastName FROM Customer WHERE ID=Select3.CustomerID

I'm scared this query will take forever. Maybe it would be best to test it by first doing one join with the first two selects, and just return the transaction number, then add the third and return the customer ID, then add the 4th and return the EMail and Name so I can test each step. I already tested the Item table query.

It also would be helpful if the result set included the Description and Price value from the Item table of the item it used to find the transaction number it used to find the customer so I can see what item that customer purchased to qualify. I'm paranoid.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 19:09:03

Can you try this...

SELECT a.Price,a.Department,b.EmailAdress,b.FirstName,b,LastName,c.ItemID,d.TransactionNumber,d.CustomerID
FROM Item a INNER JOIN TransactionEntry c on c.ItemID = a.ID
INNER JOIN Transaction d on d.TransactionNumber = c.TransactionNumber
INNER JOIN Customer b on b.ID = d.CustomerID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources