I have the following query
SELECT P.PRICE, P.PRODUCT_ID FROM PRODUCT P WHERE P.PRODUCT_ID IN (41,41)
I need it to return the product record twice due to limitations with the application code.
I am not too familiar with SQL so really stuck, I don't want to run single queries for each product
To make it appear twice, you can do:
SELECT P.PRICE, P.PRODUCT_ID FROM PRODUCT P WHERE P.PRODUCT_ID IN (41)
UNION ALL
SELECT P.PRICE, P.PRODUCT_ID FROM PRODUCT P WHERE P.PRODUCT_ID IN (41)
What the UNION operator does is it essentially "tacks" the result of one query onto the result of another... so in your case, we are "tacking" on the same exact selection so that all rows are duplicated twice in your final result.
However, what you'll probably want to do for more complex queries is use a CROSS JOIN:
SELECT P.PRICE, P.PRODUCT_ID
FROM PRODUCT P
INNER JOIN ...
INNER JOIN ...
CROSS JOIN
(
SELECT NULL UNION ALL
SELECT NULL
) cj
WHERE P.PRODUCT_ID IN (41) AND ... AND ... AND ..
This way, rows will be duplicated as many times as there are SELECT NULL's within the CROSS JOIN subselect, and the JOIN and WHERE conditions of the main query only need to be executed once.
The nice thing about using CROSS JOIN is that you don't need to evaluate a join condition, it simply makes a Cartesian product between two sets of data.
Simply tack on the CROSS JOIN at the end of all of your main JOIN operations to manually duplicate your data.
Try this::
SELECT P.PRICE, P.PRODUCT_ID FROM PRODUCT P WHERE P.PRODUCT_ID=41
UNION ALL
SELECT P.PRICE, P.PRODUCT_ID FROM PRODUCT P WHERE P.PRODUCT_ID=41
Another way (using UNION ALL in an internal level). Easier to combine with complex queries (as you mention in the comments):
SELECT p.price, p.product_id
FROM
PRODUCT AS p
JOIN
( SELECT 41 AS product_id
UNION ALL
SELECT 41
) AS selection
ON p.product_id = selection.product_id ;
UNION ALL ;)A cartesian product (vary similar to a cross-join):
SELECT P.PRICE, P.PRODUCT_ID
FROM FROM PRODUCT P
JOIN (values(1),(2) ) AS two ON 1=1
WHERE P.PRODUCT_ID IN (42)
;
