Question:
I have a SQL 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
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
Top comments (0)