How can i display SQL data only if it contains a certain value

Question

IM trying to

"Get the ID's of products ordered through any agent who takes at least one order from a customer in Kyoto, sorted by ID's from highest to lowest."

How would i write this in SQL? This is what i currently have

SELECT pid FROM orders

WHERE cid IN (

Select cid FROM customers
WHERE cid = 'c006'

);

Table: http://i.gyazo.com/dbe542fb2315d663c75c23e3ecfb74fd.png

Please don't hide important information in images, not everyone can read them. It's preferred if you embed the table schema as text within the post itself. — Kevin Brown-Silva, Mar 01 '15 at 16:39
@KevinBrown I don't know how to do that . Sorry, next time i'll be careful. — Iamwhoiam, Mar 01 '15 at 16:44

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

You can try this

`SELECT pid FROM orders WHERE cid IN ( Select cid FROM customers
 WHERE cid.city == "Kyoto") order by pid DESC;`

In this case you dont need to explicitly mention the id of customer (c006). There are several other methods also to do this using join What is the difference between "INNER JOIN" and "OUTER JOIN"? this post contains all the info needed..

SELECT pid FROM orders WHERE cid == "c006" order by pid;

that's it. whole query is to dig that cid where city == Kyoto.If I understood you correctly.

edited May 23 '17 at 12:07

Community

1
1

answered Mar 01 '15 at 17:02

kid

153
10

Your code don't work. This is my new code i tried to make. SELECT pid FROM orders WHERE aid IN( SELECT aid FROM orders WHERE cid = 'c006' ) ORDER BY pid; – Iamwhoiam Mar 01 '15 at 17:13
if you already know whats the cid of customer from kyoto then why dont you just query it from your orders table – kid Mar 01 '15 at 17:16

score 0 · Answer 2 · answered Mar 01 '15 at 17:43

0

SELECT o.pid FROM orders AS o INNER JOIN customers AS c ON o.pid = c.cid WHERE c.cid = "c006" ORDER BY p.pid DESC

I'll suggest using inner join for this query.

answered Mar 01 '15 at 17:43

Alexandru Olaru

6,842
6
27
53

bobs · Answer 3 · 2015-03-01T17:52:22.180

This may provide what you're looking for. For this request:

"Get the ID's of products ordered through any agent who takes at least one order from a customer in Kyoto, sorted by ID's from highest to lowest."

It appears that you want a list of products associated with a list of agents. The agents must have at least one order from a customer in Kyoto.

SELECT pid
FROM Orders
WHERE aid IN
    (
    SELECT o.aid
    FROM Orders o
    INNER JOIN Customers c ON o.cid = c.cid
    WHERE c.city = 'Kyoto'
    )
ORDER BY pid
;

The sub-query returns a list of agent IDs that have at least one order with a customer from Kyoto. The primary query returns the list of product IDs for the derived list of agents.

See SQLFiddle

How can i display SQL data only if it contains a certain value

3 Answers3