sql — Select Records multiple times from table
Tags: sql
Published: 2022-09-02 09:33:53

Question:
I have a [SQL](https://sqlerrors.com/) statement like this:

EDIT :

SELECT 
    location as Location 
FROM
    Table1 
WHERE 
    OnsiteOffshore = 'Offshore' AND Acc_Code = 'ABC' 

UNION 

SELECT 
    Country 
FROM 
    Table1 
WHERE
    OnsiteOffshore = 'Onsite' AND Acc_Code = 'ABC'
This SQL query gives these results:

Chennai
Bangalore
USA
NewZealand
But due to some requirement I need the output like this:

Chennai
Chennai
Chennai
Chennai
Bangalore
Bangalore
Bangalore
Bangalore
USA
USA
USA
USA
NewZealand
NewZealand
NewZealand
NewZealand
Mean to say each location needs to be output 4 times.

Pls help how to get the same.


Solution 1:

SELECT Location 
FROM Table1
  CROSS JOIN
    ( VALUES (1),(2),(3),(4)
    ) AS four(dummy)
If the 4 is not a constant but (as @xQbert noticed/asked) is the number of rows of the table, you can use this:

SELECT a.Location 
FROM Table1 AS a
  CROSS JOIN
     Table1 AS b
If you don't have Table1 but any (however complex) query, you could use this for 4 copies:

SELECT Location 
FROM (
       SELECT Location       --- complex query here
       ...                   --- inside parenthesis
     UNION 
       SELECT Country
       ...
     ) AS Table1
  CROSS JOIN
    ( VALUES (1),(2),(3),(4)
    ) AS four(dummy)
or this for n copies:

WITH cte AS
  ( SELECT Location       --- complex query here
    ...                   --- inside parenthesis
    UNION 
    SELECT Country
    ...
  )
SELECT a.Location 
FROM cte AS a
  CROSS JOIN
     cte AS b
Solution 2:

Simplest and (probably) fully accepted in any RDBMS ;-):

select location from (
    Select Location From Table1 union all
    Select Location From Table1 union all
    Select Location From Table1 union all
    Select Location From Table1
) t
order by location
And better way with CTE (Common Table Expressions):

;with cte (id) as (
    select 1 union all
    select id + 1 from cte where id < 4
)
select location from Table1
cross join cte
Solution 3:

You can create a wrapper procedure for this... where u first create a cursor for Select Location From Table1 than you can loop through this cursor and extract the data whatever way want.

optionally you can use some front end technology to do this. This is not a tough task in front end languages i.e. Java, C++, .NET or in any other popular language.

[SQL SELECT from multiple tables](https://sqlerrors.com/)
Question:
How can I get all products from customers1 and customers2 include their customer names?

customer1 table
cid name1
1   john
2   joe

customer2 table
cid name2
p1  sandy
p2  linda

product table
pid cid pname
1   1   phone
2   2   pencil
3   p1  pen
4   p2  paper
Result should be like this

pid  cid  pname  name1 name2
1    1    phone  john  NULL
2    2    pencil joe   NULL
3    p1   pen    NULL  sandy
4    p2   paper  NULL  linda

Solution 1:

SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product p
LEFT JOIN customer1 c1 ON p.cid = c1.cid
LEFT JOIN customer2 c2 ON p.cid = c2.cid
Solution 2:

SELECT pid, cid, pname, name1, name2 
FROM customer1 c1, product p 
WHERE p.cid=c1.cid 
UNION SELECT pid, cid, pname, name1, name2 
FROM customer2 c2, product p 
WHERE p.cid=c2.cid;
Solution 3:

SELECT `product`.*, `customer1`.`name1`, `customer2`.`name2`
FROM `product`
LEFT JOIN `customer1` ON `product`.`cid` = `customer1`.`cid`
LEFT JOIN `customer2` ON `product`.`cid` = `customer2`.`cid`