MySQL query for fetching result

Question

I have the following table in the database:

I need to get the row common for subcat_id=1 and subcat_id=8, i.e. the row for client_id=2.

What should be the query for that?

Edited: Lets say I have subcat_id 1 and 8. I need the client_id which has subcat_id 1and 8, in this case 1. I need the common client_id which lies in both subcat_id. Am I making more sense?

Image is not showing up. Please update so someone can help. Thanks! — Some Juan
– Some Juan, Commented Jan 30, 2016 at 11:34
I dont have the client_id. I need the client_id and I only have subcat_id. — zomato
– zomato, Commented Jan 30, 2016 at 11:48
Can you tidy up your question and make it more clear what you want? For example, supposing subcat_id with values of 1 and 8 corresponded to 2 different client_id values. Then what would you do in this case? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 30, 2016 at 12:01
No, it's not. Will every client_id - subcat_id pair always be unique? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 30, 2016 at 12:09
Perhaps you can try a nested query. Select a.client_id from tablename a where a.subcat_id = 1 and a.client_id in (select b.client_id from tablename b where b.subcat_id = 8). Where tablename refers to the same table. — Some Juan
– Some Juan, Commented Jan 30, 2016 at 12:39

Tim Biegeleisen · Accepted Answer · 2016-01-30 12:03:13Z

1

Use a WHERE clause to restrict according to what you want:

SELECT t1.client_id
FROM table t1 INNER JOIN table t2
    ON t1.client_id = t2.client_id
WHERE t1.subcat_id IN (1, 8) AND t2.subcat_id IN (1, 8)

edited Jan 30, 2016 at 12:03

answered Jan 30, 2016 at 11:38

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

user5697101 · Accepted Answer · 2016-01-30 12:02:39Z

0

Have you tried

SELECT * FROM table WHERE client_id = 2 AND ( subcat_id = 1 OR subcat_id = 8)

EDIT (2):

SELECT * 
FROM tablename a
RIGHT OUTER JOIN tablename b ON b.subcat_id =1
AND a.subcat_id = b.subcat_id
WHERE a.subcat_id = 8
GROUP BY a.client_id

edited Jan 30, 2016 at 12:02

answered Jan 30, 2016 at 11:38

user5697101

5614 silver badges12 bronze badges

3 Comments

zomato Over a year ago

I obviously dont have the client_id, else I wouldnt have asked it. I need the client_id and I only have subcat_id.

zomato Over a year ago

no that will give me client_id =1 and client_id=2. I need the row for client_id=2. The common row for subcat_id=1 and 8

user5697101 Over a year ago

@zomato I think I may have it now, my edit #2 should return all client_id's that have subcat_id = 1 and subcat_id = 8 in different rows

Collectives™ on Stack Overflow

MySQL query for fetching result

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related