CODEWITHSUNDEEP
sqldatabaseoracle-databaserdbms
Goru Chagol
Goru Chagol

Reputation: 13

SQL query from multiple tables and fetching data

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

Upvotes: 0

Views: 164

Answers (1)

hoangnh
hoangnh

Reputation: 249

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'

Upvotes: 1

Related Questions