postgres counting one record twice if it meets certain criteria

Question

I thought that the query below would naturally do what I explain, but apparently not...

My table looks like this:

id | name | g | partner | g2
1 | John | M | Sam | M
2 | Devon | M | Mike | M
3 | Kurt | M | Susan | F
4 | Stacy | F | Bob | M
5 | Rosa | F | Rita | F

I'm trying to get the id where either the g or g2 value equals 'M'... But, a record where both the g and g2 values are 'M' should return two lines, not 1.

So, in the above sample data, I'm trying to return:

$q = pg_query("SELECT id FROM mytable WHERE ( g = 'M' OR g2 = 'M' )");

1
1
2
2
3
4

But, it always returns:

1
2
3
4

Answer 1

Hi - you might try a UNION along these lines:

"SELECT id FROM mytable WHERE ( g = 'M') UNION SELECT id FROM mytable WHERE ( g2 = 'M')"

Hope this helps, Martin

Answer 2

SELECT id FROM mytable WHERE g = 'M'
UNION
SELECT id FROM mytable WHERE g2 = 'M'

Answer 3

Your query doesn't work because each row is returned only once whether it matches one or both of the conditions. To get what you want use two queries and use UNION ALL to combine the results:

SELECT id FROM mytable WHERE g = 'M'
UNION ALL
SELECT id FROM mytable WHERE g2 = 'M'
ORDER BY id

Result:

1
1
2
2
3
4