CODEWITHSUNDEEP
postgresqljointemp-tables
otterdog2000
otterdog2000

Reputation: 363

Create temp table with duplicate data

I have two tables (colors and numbers). I want to put them together in a certain format where each color has each number repeated as an entry.

My two tables:

CREATE TABLE colors (
color VARCHAR(20)
); 

INSERT INTO colors
     (color)
VALUES
     ("red"),
     ("green"),
     ("blue");

CREATE TABLE hours (
 hour NUMERIC
); 

INSERT INTO hours
     (hour)
VALUES
     (1),
     (2),
     (3),
     (4);

I want my output to look like:

Color   Hour  
red      1  
red      2
red      3
red      4
green    1
green    2
green    3
green    4
blue     1  
blue     2
blue     3
blue     4

Thanks for the help. I assume there is also some way loop way of doing this without using the numbers table. But not necessary.

Upvotes: 0

Views: 281

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are looking for a cross join:

select c.color, h.hour
from colors c cross join
     hours h;

If the ordering matters, then use an order by. This works for your example:

select c.color, h.hour
from colors c cross join
     hours h
order by c.color desc, h.hour;

Upvotes: 1

Related Questions