How to display this SQL query as one table

Question

Very basic sql question. I wan't to display the output of the following query as one table instead of two:

select players.name
from players
inner join teams
on players.team = teams.name
where teams.staysat = 'Ambassador'
and teams.checkin is not null;

select fans.name
from fans
where fans.staysat = 'Ambassador'
and teams.checkin is not null;

How can a rewrite this so the players name and fans name are printed as one continuous list?

score 8 · Accepted Answer · answered May 06 '13 at 08:02

8

You just need to put a union between them, It's so simple

select players.name
from players
inner join teams
on players.team = teams.name
where teams.staysat = 'Ambassador'
and teams.checkin is not null;
Union
select fans.name
from fans
where fans.staysat = 'Ambassador'
and teams.checkin is not null;

If you don't want to omit duplicate names you should put Union All

answered May 06 '13 at 08:02

Maryam Arshi

1,974
1
19
33

thanks, and how can I only return records up until a certain checkin date? (there is checkin/checkout date fields in teams and fans) – Martin May 06 '13 at 08:43
1

you'r welcome,If you want to do it separately You just need to add `and CheckinDate<=@Date`(whenever the Date is) and if you want to do it after union you should add CheckinDate in your select query and then at the end of whole query add `Where CheckinDate <= @Date` or use the keyword `Between` (but in this case you should have the start date as well) – Maryam Arshi May 06 '13 at 08:49
Look into these posts they are useful [How to compare a datetime type in SQL Server](http://stackoverflow.com/questions/3105031/how-to-compare-a-datetime-type-in-sql-server) and [Convert SQL server datetime fields to compare date parts only, with indexed lookups](http://stackoverflow.com/questions/353014/convert-sql-server-datetime-fields-to-compare-date-parts-only-with-indexed-look) – Maryam Arshi May 06 '13 at 08:55
thanks, one more question - Do you know if it's possible to write this with only one `select/from` line (so omitting the need for the union), or does it have to be written as above with the union? When I try this like `select players.name, fans.name` etc then add the conditions, I get errors. – Martin May 06 '13 at 09:18
As I know you should do it with two query using union or maybe you can use a loop and pass the Table_name (players & fans) as a parameter, but I prefer to write it with Union. – Maryam Arshi May 06 '13 at 09:22

score 2 · Answer 2 · answered May 06 '13 at 08:03

Try to use union all:

select players.name
from players
inner join teams
on players.team = teams.name
where teams.staysat = 'Ambassador'
and teams.checkin is not null
union all
select fans.name
from fans
where fans.staysat = 'Ambassador'
and teams.checkin is not null;

score 2 · Answer 3 · answered May 06 '13 at 08:03

Use the union statement

select players.name as name
from players
inner join teams
on players.team = teams.name
where teams.staysat = 'Ambassador'
and teams.checkin is not null;
UNION (ALL)
select fans.name as name
from fans
where fans.staysat = 'Ambassador'
and teams.checkin is not null;

score 1 · Answer 4 · answered May 06 '13 at 08:10

You can use Union for Distinct values and Union All for all values (Including Duplicates)

Union and Union All

select players.name as Name
from players
inner join teams
on players.team = teams.name
where teams.staysat = 'Ambassador'
and teams.checkin is not null;
union(all)
select fans.name as Name
from fans
where fans.staysat = 'Ambassador'
and teams.checkin is not null;

How to display this SQL query as one table

4 Answers4