0

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

Customer (cid, cname, mobile, address, gender, email)

Orders (order_no, company_name, order_date, cid, item_id, quantity)

Items (item_id, item_name, unit_price)

E_company (company_name, address, mobile)

Shipping (sid, cid, company_name, order_no, ship_date)

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.

  1. I have to fetch details of a customer along with items ordered by that customer.
  2. The shipping details of the particular order of customer named ‘X’
  3. The total order amount on ’a random date here’ for customer ‘X’

Thanks

Serg
  • 22,285
  • 5
  • 21
  • 48
Goru Chagol
  • 13
  • 4

1 Answers1

1

For example with those 2 tables :

Customer (cid, cname, mobile, address, gender, email)

Orders (order_no, company_name, order_date, cid, item_id, quantity)

You can write query like this to "fetch details of a customer along with items ordered by that customer"

Select C.*, O.* from Customer C
Left join Orders O
On C.cid = O.cid

"The shipping details of the particular order of customer named ‘X’"

Select * from Shipping S
left join Customer C
on S.cid = C.cid
and C.cname = 'X'

"The total order amount on ’a random date here’ for customer ‘X’"

Select sum(quantity)
From Orders O
Left join Customer C
On O.cid = C.cid
Where O.order_date = 'random_date' and C.cname = 'X'
hoangnh
  • 249
  • 4
  • 13
  • @GoruChagol i've just updated the answer. Please check ! – hoangnh Apr 15 '21 at 06:33
  • I have a query. cid and cname are different. here, X represents cname. not cid. Pardon me, If I could not make u understand at first. Please help. – Goru Chagol Apr 15 '21 at 06:42
  • @GoruChagol Oh ok my bad :). So basically you need to join Customer table for on Cid and make query condition with where cname = 'X'. Check update plz – hoangnh Apr 15 '21 at 06:46
  • @GoruChagol hope it worked, then you can accept the answer – hoangnh Apr 16 '21 at 08:43