CODEWITHSUNDEEP
mysqlsql
wassim
wassim

Reputation: 39

SQL query to select rows having 2 columns equal value

Let's say I have the following table:

id    || name  || published
--------------------------
1     || john  || 1
7     || alex  || 0
1     || roco  || 0
4     || mika  || 0
16    || mika  || 1
8     || zack  || 1
8     || alfi  || 1

Which sql query should I run to return IDs 16 and 8?

I tried the following but I'm always having IDs 1, 16 and 8:

SELECT A.*
FROM table A
INNER JOIN (SELECT id, published
            FROM table
            WHERE published > 0
            GROUP BY id) B
ON A.id = B.id AND A.published = B.published
GROUP BY id

Any ideas ?

Upvotes: 1

Views: 983

Answers (4)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use Group By with conditional aggregation in Having clause to consider only those where published has never been zero.

SELECT id
FROM table_name 
GROUP BY id 
HAVING SUM(published = 0) = 0

Alternatively, as @Gordon Linoff suggested, you can use the following as well:

SELECT id
FROM table_name 
GROUP BY id 
HAVING MIN(published) > 0

Upvotes: 2

Cuado
Cuado

Reputation: 721

it can be achieved using

SELECT name, published
FROM A
WHERE id IN (16, 8)
ORDER BY id;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

You could use not in

select t.* from your_table t
where t.id not in (
                  select id from your_table t1
                  where t1.published=0
                 )

output

id  name    published
16  mika    1
8   alif    1
8   zack    1

DEMO DB FIDDLE

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems want NOT EXISTS :

SELECT a.*
FROM table a
WHERE NOT EXISTS (SELECT 1 FROM table a1 WHERE a1.id = a.id AND a1.published = 0);

Upvotes: 2

Related Questions