SQL query from multiple tables and fetching data

Question

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.

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

Thanks

Thank you..but I don't know how this JOIN works. Perhaps It would be very tough for me to handle both software and database at same time.. — Goru Chagol
– Goru Chagol, Commented Apr 15, 2021 at 6:25

hoangnh · Accepted Answer · 2021-04-15 06:46:19Z

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'

edited Apr 15, 2021 at 6:46

answered Apr 15, 2021 at 6:29

hoangnh

2494 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hoangnh Over a year ago

@GoruChagol i've just updated the answer. Please check !

Goru Chagol Over a year ago

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.

hoangnh Over a year ago

@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 Over a year ago

@GoruChagol hope it worked, then you can accept the answer

Collectives™ on Stack Overflow

SQL query from multiple tables and fetching data

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related