Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 UNION or JOIN? or ??

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-16 : 10:06:34
Nathan writes "Right, we have a problem, I am making a script that selects listings from 2 tables WHERE they are of a specific kind and in a specific Area it then sorts the results by Start Date chronologicaly and then by town name alphabeticaly in that area. Just so you know, one table is just an upgraded version of the other and the 2 need to run side by side till the old table "EventsO" becomes defunct.

I am not sure wether to use a JOIN or a UNION Query to do this or maybe nested SQL Statements. The 2 SQL Statements i am using are below, as you can probably see these will just pull out records in the correct way and then display them, but will not mix the two sets of results together.

"SELECT * FROM Events WHERE AreaCode = " & SQLStr(rAreaCode) & " AND EventType = " & SQLStr(rType) & " ORDER BY StartDate, TownName ASC"

"SELECT * FROM EventsO WHERE SUB_CAT = " & SQLStr(rAreaCode) & " AND EVENT_TYPE = " & SQLStr(rType) & " ORDER BY EVENT_DATE_START ASC, EVENT_LOCATION ASC"

Should I use ALIASES inside a UNION statement so I can still sort the results and ORDER BY Start Date & Town Name, or just a join on the AreaCode and EventType columns in each table.

You see I need to pull out about 15 fields of data from each table, whose column names are differant in each table.. hence the * to select all fields. So really i will have to alias all the fields i am picking out the tables so they have the same name.

Any Help would be much apreciated and is needed ASAP..

Thanks Nathan"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-16 : 10:57:55
It sounds like you need a UNION, not a JOIN.
quote:

UNION operator
Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. This is different from using joins that combine columns from two tables.

Two basic rules for combining the result sets of two queries with UNION are:

The number and the order of the columns must be identical in all queries
The data types must be compatible.



Without some DDL, it is tough for me to write the query for you, but this example should help you out...

create table #tablea (somefield int)
create table #tableb (someotherfield int)

insert #tablea values(1)
insert #tablea values(2)
insert #tablea values(3)
insert #tablea values(4)

select
somefield
from
#tablea
union
select
someotherfield
from
#tableb
order by somefield

 


<O>
Go to Top of Page
   

- Advertisement -