0

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?

  • Add WHERE unitPrice = 1500 at the end – Jacob H Jun 21 '19 at 17:00
  • Noh, I tried, it doesn't work, gives error '' Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'WHERE'." @Jacob H – Aaby Jun 21 '19 at 17:36
2

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   |
  • Yes it does, but all the data of tables have to be entered to the query sounds difficult as if there many records in tables, Is there any ways to retrieve data from specific tables(Product & Supplier) to declared tables without entering data in query? @Scott Hodgin – Aaby Jun 21 '19 at 18:34
  • 1
    @Aaby- I merely used table variables @Product and @Suppliers to demo my solution. Look at my answer starting at the comment --solution and replace my table variables with your real tables. – Scott Hodgin Jun 21 '19 at 20:02
  • Yes, I got it.. :) Thank you so much genius..@Scott Hodgin – Aaby Jun 22 '19 at 16:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.