CODEWITHSUNDEEP
mysqlsqljunction-tablejunction
Michael Higgon
Michael Higgon

Reputation: 21

SQL Junction query with 3 table references

I'm looking to identify from my data input all wine bottles purchased from danmurphys that were produced in 2012. My query syntax isn't right and I'm not sure what I'm doing wrong.

Your help would be much appreciated :)

CREATE TABLE Retailer
(RETAIL_ID NVARCHAR(4) NOT NULL,
RETAIL_NAME NVARCHAR(30) NOT NULL,
PRIMARY KEY (RETAIL_ID));

CREATE TABLE Wine
(WINE_ID NVARCHAR(3) NOT NULL,
WINE_NAME NVARCHAR(30) NOT NULL,
WINE_VINT NVARCHAR(4) NOT NULL,
PRIMARY KEY(WINE_ID));

CREATE TABLE Dist
(RETAIL_ID NVARCHAR(4) NOT NULL,
WINE_ID NVARCHAR(3) NOT NULL,
WINE_PRICE DECIMAL(6,2) NOT NULL,
CONSTRAINT PK_Dist PRIMARY KEY
    (
        RETAIL_ID,
        WINE_ID
    ),
    FOREIGN KEY (RETAIL_ID) REFERENCES Retailer (RETAIL_ID),
    FOREIGN KEY (WINE_ID) REFERENCES Wine (WINE_ID));


INSERT INTO Wine VALUES('101','Grange','2010');
INSERT INTO Wine VALUES('102','Grange','2006');
INSERT INTO Wine VALUES('103','Reserve Shiraz','2013');
INSERT INTO Wine VALUES('104','Grey Label Shiraz','2012');
INSERT INTO Wine VALUES('105','Patricia Shiraz','2009');
INSERT INTO Wine VALUES('106','Ten Acres Shiraz','2012');
INSERT INTO Wine VALUES('107','Double Barrel Shiraz','2012');
INSERT INTO Wine VALUES('108','Platinum Label Shiraz','2006');

INSERT INTO Retailer VALUES('1001', 'Dan Murphys');
INSERT INTO Retailer VALUES('1002', 'Woolworths');

INSERT INTO Dist VALUES('1001','101','750');
INSERT INTO Dist VALUES('1001','102','700');
INSERT INTO Dist VALUES('1001','103','10');
INSERT INTO Dist VALUES('1001','104','35');
INSERT INTO Dist VALUES('1001','105','50');
INSERT INTO Dist VALUES('1001','106','25');
INSERT INTO Dist VALUES('1001','107','15');
INSERT INTO Dist VALUES('1001','108','170');
INSERT INTO Dist VALUES('1002','103','9');
INSERT INTO Dist VALUES('1002','104','33');
INSERT INTO Dist VALUES('1002','105','44');

SELECT Wine.WINE_NAME, Dist.WINE_PRICE, Retailer.RETAIL_NAME
FROM Wine
INNER JOIN Dist ON (Dist.RETAIL_ID = Retailer.RETAIL_ID)
INNER JOIN Retailer on (Dist.RETAIL_ID = Retailer.RETAIL_ID)
WHERE Retailer.RETAIL_ID = '1001' AND Wine.WINE_VINT = '2012'

Upvotes: 2

Views: 79

Answers (1)

jarlh
jarlh

Reputation: 44696

Changed JOIN condition between Wine and dist:

SELECT Wine.WINE_NAME, Dist.WINE_PRICE, Retailer.RETAIL_NAME
FROM Wine
INNER JOIN Dist ON (Dist.wine_id = wine.WINE_ID)
INNER JOIN Retailer on (Dist.RETAIL_ID = Retailer.RETAIL_ID)
WHERE Retailer.RETAIL_ID = '1001' AND Wine.WINE_VINT = '2012'

Turns out as:

SQL>SELECT Wine.WINE_NAME, Dist.WINE_PRICE, Retailer.RETAIL_NAME
SQL&FROM Wine
SQL&INNER JOIN Dist ON (Dist.wine_id = wine.WINE_ID)
SQL&INNER JOIN Retailer on (Dist.RETAIL_ID = Retailer.RETAIL_ID)
SQL&WHERE Retailer.RETAIL_ID = '1001' AND Wine.WINE_VINT = '2012';
WINE_NAME                      WINE_PRICE RETAIL_NAME
============================== ========== ==============================
Grey Label Shiraz                   35.00 Dan Murphys
Ten Acres Shiraz                    25.00 Dan Murphys
Double Barrel Shiraz                15.00 Dan Murphys

                  3 rows found

Upvotes: 1

Related Questions