CODEWITHSUNDEEP
sqldatabaseoracle-database
Goru Chagol
Goru Chagol

Reputation: 13

SQL querying from multiple tables and retrieving data

I have the following tables which are represented in form of schema :

The problem is I don't know how can I search data from multiple tables and represent it in my software interface. Although I can represent it but I can't retrieve the data from the tables. So, here are the required data I needed to fetch.

I have to fetch:

  1. Names of customers who’ve ordered item with id 100.
  2. Names of Customers who’ve ordered a laptop (i.e., an item of type “laptop”).
  3. Customers who’ve ordered a laptop and a desktop computer. The names of customers who’ve ordered all item.

Upvotes: 0

Views: 560

Answers (2)

Andreas
Andreas

Reputation: 159086

  1. Names of customers who’ve ordered item with id 100.

We can do it by first running a query to find customer id's with an order that includes the given item id, then lookup the customers name. This is done with an IN clause.

SELECT c.cname
  FROM Customer c
 WHERE c.cid IN (
          SELECT o.cid
            FROM Order o
           WHERE o.iid = 100
       )

A customer will only be returned once, that is guaranteed by the Set semantics of the IN clause, but the same customer name might be returned multiple times, if the database allows multiple customers with the same name.

To only return the customer name once, even if there are multiple customers with the same name, we can use the DISTINCT clause. If we do that, we can change the query to use a normal JOIN.

SELECT DISTINCT c.cname
  FROM Order o
  JOIN Customer c ON c.cid = o.cid
 WHERE o.iid = 100

Which is better is a matter of taste, I believe they'll perform about the same. Personally, I prefer the first one.

  1. Names of Customers who’ve ordered a laptop (i.e., an item of type “laptop”).

Same as before, but staring with a WHERE condition against the Item table.

SELECT c.cname
  FROM Customer c
 WHERE c.cid IN (
          SELECT o.cid
            FROM Item i
            JOIN Order o ON o.iid = i.iid
           WHERE i.type = 'laptop'
       )

SELECT DISTINCT c.cname
  FROM Item i
  JOIN Order o ON o.iid = i.iid
  JOIN Customer c ON c.cid = o.cid
 WHERE i.type = 'laptop'
  1. Customers who’ve ordered a laptop and a desktop computer.
    The names of customers who’ve ordered all item

When using IN clause, we just need to do it twice, to find customers that ordered both.

SELECT c.cname
  FROM Customer c
 WHERE c.cid IN (
          SELECT o.cid
            FROM Item i
            JOIN Order o ON o.iid = i.iid
           WHERE i.type = 'laptop'
       )
   AND c.cid IN (
          SELECT o.cid
            FROM Item i
            JOIN Order o ON o.iid = i.iid
           WHERE i.type = 'desktop'
       )

For the other way, we need to use GROUP BY instead of DISTINCT so we can check that customer ordered both.

SELECT DISTINCT c.cname
  FROM Item i
  JOIN Order o ON o.iid = i.iid
  JOIN Customer c ON c.cid = o.cid
 WHERE i.type IN ('laptop', 'desktop')
 GROUP BY c.cid, c.cname
HAVING COUNT(DISTINCT i.type) = 2

Or we can mix the two styles.

SELECT c.cname
  FROM Customer c
 WHERE c.cid IN (
          SELECT o.cid
            FROM Item i
            JOIN Order o ON o.iid = i.iid
           WHERE i.type IN ('laptop', 'desktop')
           GROUP BY o.cid
          HAVING COUNT(DISTINCT i.type) = 2
       )

The third one is likely the best for performance.

Upvotes: 0

Sahil Aggarwal
Sahil Aggarwal

Reputation: 1351

Your questions:

  1. Names of customers who’ve ordered item with id 100.

    select name from customer where cid in (select distinct(cid) from order where iid= 100) ;

  2. Names of Customers who’ve ordered a laptop (i.e., an item of type “laptop”).

    select name from customer where cid in (select distinct(cid) from order where iid=(select iid from item where type='laptop')) ;

  3. Customers who’ve ordered a laptop and a desktop computer. The names of customers who’ve ordered all item

    select name from customer where cid in (select distinct(cid) from order where iid in (select iid from item where type in ('laptop','desktop') )) ;

Upvotes: 1

Related Questions