CODEWITHSUNDEEP
mysql
Louie Almeda
Louie Almeda

Reputation: 5612

mysql: get a row for every matching condition even with the same record

I know this is weird (maybe?) But how do I get multiple rows for the same record, relative to how many condition it passed?

table foo
+----------+-------+
| field1   |field2 |
+----------+-------+
| 1        | 0     |
+----------+-------+

SELECT field1
FROM foo
WHERE field1 = 1 OR field2 = 0

I want it to return

+----------+
| field1   |
+----------+
| 1        |
| 1        |
+----------+

Upvotes: 1

Views: 38

Answers (2)

Andriy M
Andriy M

Reputation: 77687

Another way is to do something like this:

SELECT
  t.field1
FROM
  foo AS t
CROSS JOIN
  (SELECT 1 AS colID UNION ALL SELECT 2) AS x
WHERE
  x.colID = 1 AND t.field1 = 1
  OR
  x.colID = 2 AND t.field2 = 0
;

By cross-joining the subselect you are duplicating the rows. Each duplicate is distinguished by a x.colID value. Using that value you can apply every partial condition independently, as you can see done in the WHERE clause above.

Upvotes: 0

void
void

Reputation: 7890

one way is to break the conditions in the where clause and use UNION ALL:

SELECT field1
 FROM foo
 WHERE field1 = 1
UNION ALL
SELECT field1
 FROM foo
 WHERE field2 = 0

Upvotes: 2

Related Questions