Slecting specific query returnned data related records from another table [closed]

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 1

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   |