How to make a SELECT keeping the first column until the records are finished

Asked

Viewed 231 times

0

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

Let me give you an example to illustrate it better, because it’s hard to transcribe it.

I want to list for 3 people all 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 don’t have much practice with SQL, so I’m picking up a bit to make this case. The example is just to illustrate the problem.

sql sql-server
  • 1

    How would you be saving these fields from your example? In a single table? In two tables linked by foreign key?

  • So that’s one of the points I’m seeing as a problem. They are different tables, but have no foreign key connection, are totally independent.

  • In a larger and future context, in all cases will it be so that all people relate to all stadiums? Or there may be the case of the Xunga person who will only be related to some stadiums and not all?

  • I launched an answer considering ALL to ALL, otherwise you will have to specify the conditions (as David questioned) and deal with Where.

  • @Davidalves I believe it is a need only punctual, but always thinking of all people relate to all stadiums. Ismael and Ricardo answered this case. I appreciate your help as well.

2 answers

1


You can use the junction CROSS, that is, create a cross-junction of your data and with it, you will be able to display ALL table records estadios for each table record pessoa.

Code

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

    Perfect, it was just what I needed. Thank you.

1

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

Just make a select with the fields, including both tables in the from without bringing them together (without join), thus:

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

See the example in sqlfiddle: http://sqlfiddle.com/#! 18/d070e/3

  • Thank you very much. It really worked out the way I needed to.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.