CODEWITHSUNDEEP
sql
voi tech
voi tech

Reputation: 11

How can I select multiple records from more that two tables

I am a complete beginner in SQL and need some help to learn. I have this learning material I found online and trying to write queries per the instructions. I did the first but got stuck on the rest of the queries. Can anyone please help me write any one of the queries below? [This is the design of my database tables]

Please help me learn how to do the rest.

b) Get aids and names of agents who have placed individual orders of at least $500 for any customer living in Kyoto. c) What cities do those customers live in, who enjoy discount under 10% and have ordered at least 1000 combs so far (in all their orders put together)? d) What product names have been ordered by at least one customer based in Dallas through any agent based in Tokyo? e) Get the name, city, and total dollar amount of all orders placed by each customer, arranged in descending order of the amounts.

Here is how I managed to do the first one.

a) Get pairs of all customer cids and agent aids, who live in the same city.

SELECT customer.cid, agent.aid FROM customers, agent WHERE customer.city = agent.city

RESULT

cid aid
c001 a05
c004 a05
c002 a06
c003 a06

Upvotes: 1

Views: 103

Answers (1)

philip nyankena
philip nyankena

Reputation: 29

Try this one for b

 SELECT distinct Agents.City -- Distinct becuase you dont want more then one of the same city being returned
    FROM Agents
    JOIN Orders
    ON orders.aid = agents.aid
    WHERE orders.cid = 'c002'  -- Checks the orders table for any cid equal to c002

Upvotes: 1

Related Questions