Geek asks and answers

Internal join of one field with several columns in a table

I have an SQL table that stores customer personal data, such as FirstName, LastName, OGCard; OGCard - a field indicating the number of a personal customer card.

Next, I have a table called "Maps" that contains data on ten items that were purchased in the following format: OGCard, Item1ID, ..., Item10ID, Item1Quantity, ..., Item10Quantity

And in the elements of the table I got this structure ItemID, ItemName

I want to get all purchases from CardNumber

I think I should do something like this

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

Thus, I see the name of the first element and its quantity. However, I cannot make the same connection to the column Item2ID, etc. Could you offer me some solution?

+3
2

, :

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

- Items_n_ID , LEFT JOIN INNER JOIN, .

, .

+4

, Items, :

SELECT Buyers.FirstName
     , Items.Name
     , Cards.Item1Quantity 
     , C2.Item2Quantity
     , C3.Item3Quantity
     , C4.Item4Quantity
     , C5.Item5Quantity
     , C6.Item6Quantity
     , C7.Item7Quantity
     , C8.Item8Quantity
     , C9.Item9Quantity
     , C10.Item10Quantity 
  FROM Buyers 
 INNER JOIN Cards ON Buyers.OGCard=Cards.OGCard 
 INNER JOIN Items ON Cards.Item1ID=Items.ItemName  
  LEFT OUTER JOIN Items ON Cards.Item2D=Items.ItemName C2
  LEFT OUTER JOIN Items ON Cards.Item3D=Items.ItemName C3
  LEFT OUTER JOIN Items ON Cards.Item4D=Items.ItemName C4
  LEFT OUTER JOIN Items ON Cards.Item5D=Items.ItemName C5
  LEFT OUTER JOIN Items ON Cards.Item6D=Items.ItemName C6
  LEFT OUTER JOIN Items ON Cards.Item7D=Items.ItemName C7
  LEFT OUTER JOIN Items ON Cards.Item8D=Items.ItemName C8
  LEFT OUTER JOIN Items ON Cards.Item9D=Items.ItemName C9
  LEFT OUTER JOIN Items ON Cards.Item10D=Items.ItemName C10
 WHERE OGCard=13451

, , , , ...

+6

More articles:


All Articles