Question :
Product table:
productId | productName | unitPrice | supplierId
_______________________________________________
1 | Iphone8 | 1500 | 3
2 | Nokia 6 | 800 | 1
3 | Samsung S9 | 1500 | 2
Supplier table:
supplierId | supplierName | country
___________________________________
1 | Nokia Inc | India
2 | Samsung Inc | Korea
3 | Apple Inc | USA
I’m confused, how can I write a query using above two Product
and Supplier
tables to list the product unitprice 1500 only and their supplier Id
, name
and country
. Such as the table below shows.
productId | unitPrice| supplierId |supplierName | country
_____________________________________________
1 | 1500 | 3 | Apple Inc | USA
3 | 1500 | 2 | Samsung Inc | Korea
I used join queries with supplierId
on product
table to get its relating data from the supplier
table. But each time I ended up in error when I try to fetch ‘= 1500’ to this query.
this is the query I tried;
select *
from Product, Supplier
where Product.supplierId = Supplier.supplierId
but it gives all the records, how can I insert “unitPrice = 1500” to this query to achieve my answer?
Answer :
See if this give you the desired results.
--demo setup
DECLARE @ProductTable TABLE (
productId INTEGER,
productName VARCHAR(10),
unitPrice INTEGER,
supplierId INTEGER
);
INSERT INTO @ProductTable
(productId, productName, unitPrice, supplierId)
VALUES
('1', 'Iphone8', '1500', '3'),
('2', 'Nokia 6', '800', '1'),
('3', 'Samsung S9', '1500', '2');
DECLARE @SupplierTable TABLE (
supplierId INTEGER,
supplierName VARCHAR(11),
country VARCHAR(5)
);
INSERT INTO @SupplierTable
(supplierId, supplierName, country)
VALUES
('1', 'Nokia Inc', 'India'),
('2', 'Samsung Inc', 'Korea'),
('3', 'Apple Inc', 'USA');
--solution
SELECT p.productId
,p.unitPrice
,p.supplierId
,s.supplierName
,s.country
FROM @ProductTable P
JOIN @SupplierTable s
ON s.supplierId = P.supplierId
where p.unitPrice=1500
order by p.productId
| productId | unitPrice | supplierId | supplierName | country |
|-----------|-----------|------------|--------------|---------|
| 1 | 1500 | 3 | Apple Inc | USA |
| 3 | 1500 | 2 | Samsung Inc | Korea |