SELECT returning X times the same results-CodePudding

I need to return the same results of a SELECT request, but with only 1 field change (email).

SELECT `field1`, '[email protected]' as email, `nom`, `prenom`
FROM my_table
WHERE field1 = 1
AND field2 = 2
LIMIT 200

Return

1,[email protected],Valjean,Jean
1,[email protected],Tran,Jerome
1,[email protected],Doe,John

I need:

1,[email protected],Valjean,Jean
1,[email protected],Tran,Jerome
1,[email protected],Doe,John
1,[email protected],Valjean,Jean
1,[email protected],Tran,Jerome
1,[email protected],Doe,John

In a single request. Thanks for help

CodePudding user response：

You could use UNION (which will remove duplicates in your case there aren't any duplicates )

SELECT `field1`, '[email protected]' as email, `nom`, `prenom`
FROM my_table
WHERE field1 = 1
AND field2 = 2
LIMIT 200
UNION 
SELECT `field1`, '[email protected]' as email, `nom`, `prenom`
FROM my_table
WHERE field1 = 1
AND field2 = 2
LIMIT 200