JOIN - find list of purchased products

[TeeBar]

I have four tables and am trying to get a list of all products ever purchased. The tables are
Guest - GuestID
Ticket - GuestID, TicketID
TicketItem - ProductID,TicketID,TicketItemID
Product - productID

What is a query that would give me a list of all products purchased by one person?

I got a count of items this way but can't ge tthe list of products.

SELECT G.GuestID
, COUNT(TL.ProductID) Purchases
, SUM(TL.Quantity) ProductsPurchased
FROM dbo.Guest G
INNER JOIN dbo.Ticket T ON G.guestid = T.guestid
INNER JOIN dbo.TicketItem TL ON T.ticketID = TL.ticketID
WHERE G.GuestID = '1234'
GROUP BY G.guestID
HAVING COUNT(TL.ProductID) > 0
ORDER BY ProductsPurchased

 

[TheBugSlayer]

You' re almost there. Assuming purchased products appear in the TicketItem table, you need to SELECT all from TicketItem; the relationship between Product and Person is through TicketID, so JOIN the Ticket table on TicketID; with that you can get the Person (Guest) by JOINing Guest on GuestID. Show us what you get and if you need further help I am sure someone will help.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TeeBar]

Thanks for that, I think I almost have it but I cannot get a query that executes without errors. How do I select * within a join?

 

[TeeBar]

NM, sorry. I have it all wrong and need to read about some querying. thanks for your help

 

[TheBugSlayer]

No problem. Say what you want in the simplest english form, like you did, and try to write it in SQL:

SELECT ti.*, g.GuestID FROM TicketItem ti
INNER JOIN Ticket t ON ti.TicketID = t.TicketID
INNER JOIN Guest g on t.GuestID = t.GuestID
--JOIN Product p ON ti.ProductID = p.ProducID
WHERE g.GuestID = @GuestID

This will give you all products by customer @GuestID, repeating those the customer has purchased more than once. If you want each product to appear only once you need to do some grouping.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)