Joining and ordering different recordsets into 1 recordset

Joining and ordering different recordsets into 1 recordset

Post by J » Sat, 05 Oct 2002 00:58:15



I need to join the following recordsets and output into 1 recordset.
As you can see all output column headers the same.
The poblem I am having is thinking in terms of combining three
distinct recordset but outputing and ordering as one recordset.
A way of thinking about this problem, would be appreciated

select eventsLiveId as id,goLiveDateTime,t_eventsLive.title as
title,'event' as type from t_eventsLive order by goLiveDateTime
select Id as id, goLiveDateTime,t_newsLive.title as title,'news' as
type from t_newsLive  order by goLiveDateTime
select id as id, goLiveDateTime,t_pressReleasesLive.title as title ,
'pressrelease' as type from t_pressReleasesLive order by
goLiveDateTime

 
 
 

Joining and ordering different recordsets into 1 recordset

Post by Mike Wad » Sat, 05 Oct 2002 01:14:50


Try this:

SELECT .......FROM .......

UNION

SELECT ..... FROM ....

UNION

SELECT ...... FROM......

ORDER BY XXXXX

Quote:>-----Original Message-----
>I need to join the following recordsets and output into 1
recordset.
>As you can see all output column headers the same.
>The poblem I am having is thinking in terms of combining
three
>distinct recordset but outputing and ordering as one
recordset.
>A way of thinking about this problem, would be appreciated

>select eventsLiveId as

id,goLiveDateTime,t_eventsLive.title as
Quote:>title,'event' as type from t_eventsLive order by
goLiveDateTime
>select Id as id, goLiveDateTime,t_newsLive.title as
title,'news' as
>type from t_newsLive  order by goLiveDateTime
>select id as id, goLiveDateTime,t_pressReleasesLive.title
as title ,
>'pressrelease' as type from t_pressReleasesLive order by
>goLiveDateTime
>.


 
 
 

Joining and ordering different recordsets into 1 recordset

Post by Narayana Vyas Kondredd » Sat, 05 Oct 2002 01:20:30


You could use UNION. See Books Online for more info.

--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Quote:> I need to join the following recordsets and output into 1 recordset.
> As you can see all output column headers the same.
> The poblem I am having is thinking in terms of combining three
> distinct recordset but outputing and ordering as one recordset.
> A way of thinking about this problem, would be appreciated

> select eventsLiveId as id,goLiveDateTime,t_eventsLive.title as
> title,'event' as type from t_eventsLive order by goLiveDateTime
> select Id as id, goLiveDateTime,t_newsLive.title as title,'news' as
> type from t_newsLive  order by goLiveDateTime
> select id as id, goLiveDateTime,t_pressReleasesLive.title as title ,
> 'pressrelease' as type from t_pressReleasesLive order by
> goLiveDateTime

 
 
 

Joining and ordering different recordsets into 1 recordset

Post by Anith Se » Sat, 05 Oct 2002 01:20:43


Always post table DDLs(CREATE TABLE statements) and sample
data (preferably as INSERT statements) along with expected
results as a readable list, so that others can understand &
possibly create a test scenario of what you are trying to do.

You can use an UNION between your SQL statements as:

SELECT ..., 'event' AS type  FROM t_eventsLive
UNION ALL
SELECT ..., 'news' AS type  FROM t_newsLive
UNION ALL
SELECT ..., 'pressrelease' AS type  FROM t_pressReleASesLive
ORDER BY goLiveDateTime

--
- Anith

 
 
 

Joining and ordering different recordsets into 1 recordset

Post by Robert Carneg » Sat, 05 Oct 2002 23:41:27



> Always post table DDLs(CREATE TABLE statements) and sample
> data (preferably as INSERT statements) along with expected
> results as a readable list, so that others can understand &
> possibly create a test scenario of what you are trying to do.

> You can use an UNION between your SQL statements as:

> SELECT ..., 'event' AS type  FROM t_eventsLive
> UNION ALL
> SELECT ..., 'news' AS type  FROM t_newsLive
> UNION ALL
> SELECT ..., 'pressrelease' AS type  FROM t_pressReleASesLive
> ORDER BY goLiveDateTime

I didn't think you'd necessarily need "AS" column names for
later tables in the UNION.  Don't they just line up by column
order?  (And probably throw an error if types are beyond
implicit conversion.)  Hmm...yes (SQL Server 2000):

create table #f (number tinyint,forename char(10))
create table #s (surname char(10),number int)

insert #f values (1,'Robert')
insert #f values (2,'John')
insert #f values (3,'Alexander')
insert #s (number,surname) values (10,'Carnegie')
insert #s (number,surname) values (20,'Robertson')
insert #s (number,surname) values (30,'Fleming')

-- Requires same number of columns, uses first select's names,
-- but does not necessarily return first select's results first.
--
select forename,0 from #f
union
select * from #s

forename              
---------- -----------
Alexander  0
Carnegie   10
Fleming    30
John       0
Robert     0
Robertson  20

(My first draft used names of colleagues which I decided to take
out, and gave me three surnames first in a column called 'Forename'.)

Just an odd little detail or two.

 
 
 

Joining and ordering different recordsets into 1 recordset

Post by Anith Se » Sun, 06 Oct 2002 00:07:38


Quote:>> I didn't think you'd necessarily need "AS" column names for

later tables in the UNION.  Don't they just line up by column
order?   <<

Yes, they do. You can avoid in all SELECT statements except the
first one.

Quote:>> And probably throw an error if types are beyond implicit

conversion.)   <<

I see your point in the example you provided. But the implicit
conversion is not applicable in this case, since the query is
just using constant character values. Good point though :-)

--
- Anith

 
 
 

1. Reading columns in recordset row in different order

With Java, on setting up a recordset using the
default parameters, I have found that I can only
read columns in the order they appear in the
query result.

Therefore, the following statement would work :

System.out.println(rs.getString(1) + rs.getString
(2)+ rs.getString(3));

... but this would not :

System.out.println(rs.getString(1) + rs.getString
(3)+ rs.getString(2));

Is there a way of making the second statement
work??

(I have tried setting different parameters on the
statement, e.g.
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR
_UPDATABLE, but these have given run-time errors,
so I don't know if this is the solution).

Please help.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Grace Hopper Celebration of Women in Computing

3. Recordset--join tables in different data sources?

4. many tables, HELP please

5. Querying two recordset objects or Joining tables from different datastores

6. Changes made under one user not visible with other....

7. Please help: Joining recordsets from different SQL databases in Crystal

8. Bulk Insert

9. Creating a new recordset by joining 2 existing recordsets

10. Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

11. SELECT returns different recordsets in different tools?!

12. SP - Only select columns which names are in a different recordset

13. ADO Recordsets ByReference to Different DLLs