How to get data from two tables in the same column?

Navigation
1

I have two tables in the database: 

Tabela1
Id      Nome       Sexo
1       NomeA      Macho
2       NomeC      Fêmea

Tabela2
Id      Nome       Sexo
1       NomeB      Macho
2       NomeD      Fêmea

I would like to have the following result: 

Id      Nome       Sexo       Tabela
1       NomeA      Macho      Tabela1
2       NomeB      Fêmea      Tabela2
1       NomeC      Macho      Tabela1
2       NomeD      Fêmea      Tabela2

Notice that it is in alphabetical order of the two tables.

How could I make a SELECT on SQL Server to return a result like this?

Using Linq with Entity Framework , is it possible to do the same? (In this case you have db.Table1 and db.Table2 )

    
asked by anonymous 30.07.2015 / 18:54

2 answers

4

If the columns are the same, you can use SELECT with UNION (if you want to avoid duplication) or UNION ALL if you want to display the result even with duplicates: 

SELECT ID, NOME, SEXO, 'Tabela1' as TABELA
FROM TABELA1
ORDER BY NOME

UNION 

SELECT ID, NOME, SEXO, 'Tabela2' as TABELA
FROM TABELA2
ORDER BY NOME

No Entity Framework: 

var uniao = db.Tabela1.Select(new {
    Id = Id,
    Nome = Nome,
    Sexo = Sexo,
    Tabela = 'Tabela1'
}).ToList().Union(db.Tabela2.Select(new {
    Id = Id,
    Nome = Nome,
    Sexo = Sexo,
    Tabela = 'Tabela2'
}).ToList()).OrderBy(x => x.Nome);
    
30.07.2015 / 19:05
0

One option I would have is to create a View in SQL Server: 

CREATE VIEW vw_Tabelas AS
  SELECT
      [Id]
    , [Nome]
    , [Sexo]
    , 'Tabela1' AS [Tabela]
  FROM
    Tabela1

  UNION

  SELECT
      [Id]
    , [Nome]
    , [Sexo]
    , 'Tabela2'
  FROM
    Tabela2

So your query ends up just like this: 

SELECT [Id], [Nome], [Sexo], [Tabela] FROM vw_Tabelas ORDER BY [Nome]

And LINQ equals: 

var resultados = db.vw_Tabelas.Select().ToList().OrderBy(x => x.Nome);
    
31.07.2015 / 06:27
Versioning project with Visual Studio 2013 and 2015 Access more specific data of an object