inner join one field to multiple columns in table

sql, sql-server-2008

Solution

It's possible to join the same table twice by using aliases:

SELECT
    Buyers.FirstName,
    Items1.Name, Cards.Item1Quantity,
    Items2.Name, Cards.Item2Quantity,
    Items3.Name, Cards.Item3Quantity,
    Items4.Name, Cards.Item4Quantity
FROM Buyers
INNER JOIN Cards ON Buyers.OGCard=Cards.OGCard
INNER JOIN Items AS Items1 ON Cards.Item1ID=Items.ItemName
INNER JOIN Items AS Items2 ON Cards.Item2ID=Items.ItemName
INNER JOIN Items AS Items3 ON Cards.Item3ID=Items.ItemName
INNER JOIN Items AS Items4 ON Cards.Item4ID=Items.ItemName
WHERE OGCard=13451

If some of the `Items_n_ID`s can be missing, you could use a `LEFT JOIN` instead of an `INNER JOIN` so that you still get the results for the values that are present.

However I'd strongly recommend you to normalize your database rather than going with this approach.

Problem

I have SQL table that holds buyers personal data such as FirstName, LastName, OGCard; OGCard - field that indicates clients personal card number. Next I have table called Cards which contains data about ten items that were purchased in the following format: OGCard, Item1ID,...,Item10ID,Item1Quantity,...,Item10Quantity And in table Items I got such structure ItemID, ItemName I want to get all purchases from CardNumber I think i should do something like that ``` SELECT Buyers.FirstName, Items.Name, Cards.Item1Quantity FROM Buyers INNER JOIN Cards ON Buyers.OGCard=Cards.OGCard INNER JOIN Items ON Cards.Item1ID=Items.ItemName WHERE OGCard=13451 ``` In this way I can see the name of first item and it's quantity. However i cannot perform the same join to column Item2ID, and so on. Could you suggest me some solution?

Original source