How to do a SELECT while keeping the first column until all records

Question

How to do a SELECT while keeping the first column until all records

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I would like to do a SELECT that would repeat the first column, but when all the records were over, I would change the data in the first column and repeat all the records again.

I'll give you an example to illustrate better, as it's complicated to transcribe this.

I want to list for 3 people all the stadiums for example for each of them:

Tabela Pessoa:
Id - Identificação
1 - Fulano
2 - Ciclano
3 - Beltrano

Tabela Estadio
1 - Allianz Arena
2 - Santiago Bernabeu
3 - Camp Nou

SELECT desired:

Id - Pessoa - Estadio
1 - Fulano - Allianz Arena
1 - Fulano - Santiago Bernabeu
1 - Fulano - Camp Nou
2 - Ciclano - Allianz Arena
2 - Ciclano - Santiago Bernabeu
2 - Fulano - Camp Nou
3 - Beltrano - Allianz Arena
3 - Beltrano - Santiago Bernabeu
3 - Beltrano - Camp Nou

I do not have much practice with SQL, so I'm catching up a little to make this case. The example is just to illustrate the problem.

sql sql-server

asked by anonymous 28.02.2018 / 12:50

2 answers

1

What you want to do is what you usually want to avoid in a query with more than one table, a Cartesian table. You want to combine all Person data with Stadium

Simply make a select with fields, including both tables in from without joining them (without doing join ), like this:

select id, identificacao, descricaoEstadio
from Pessoa, Estadio
order by id, identificacao

See the example in sqlfiddle: link

28.02.2018 / 13:03

Table filled by other tables Why does '08' generate error within the Eclipse?

score 1 · Accepted Answer

You can use the CROSS join, or create a cross-join of your data and you will be able to display ALL records of the estadios table for each record in the pessoa table.

Code

SELECT P.ID, P.NOME, E.NOME FROM PESSOAS P
CROSS JOIN ESTADIOS E
ORDER BY P.ID