Mix two tables in a single SELECT statement - not a join

[bizt]

Hi,

I have an Intranet with the organisation I work with. In this
organisation there are two departments - community department and press
office. Both the community department and press office have thier own
news system where they can add news in their own admin section that
will appear on their homepages. Both admin section have their own DB
table thus two seperate tables.

I would like to select all the rows from both tables in a single SELECT
statement so when I output them they will be integrated. This is not
the same as a table join as their are no keys and I want the rows from
both to remain seperate. Can this be done? Could someone please post an
example SQL statement demonstrating this if so?

Thanks

Burnsy

[Oli Filth]

bizt said the following on 07/11/2005 11:24:

I would like to select all the rows from both tables in a single SELECT
statement so when I output them they will be integrated. This is not
the same as a table join as their are no keys and I want the rows from
both to remain seperate. Can this be done? Could someone please post an
example SQL statement demonstrating this if so?

UNION

--
Oli

[Erwin Moller]

bizt wrote:

Hi,

I have an Intranet with the organisation I work with. In this
organisation there are two departments - community department and press
office. Both the community department and press office have thier own
news system where they can add news in their own admin section that
will appear on their homepages. Both admin section have their own DB
table thus two seperate tables.

I would like to select all the rows from both tables in a single SELECT
statement so when I output them they will be integrated. This is not
the same as a table join as their are no keys and I want the rows from
both to remain seperate. Can this be done? Could someone please post an
example SQL statement demonstrating this if so?

Thanks

Burnsy

Hi Burnsy,

Do you want to use two different datasources in 1 single query?
I think you are making things overcomplicated.

Why don't you query both datasources seperately, and then in PHP decide
which one to show in your unified 'resultset' ?

Regards,
Erwin Moller

[Steve]

I would like to select all the rows from both tables in a single SELECT
statement so when I output them they will be integrated. This is not
the same as a table join as their are no keys and I want the rows from
both to remain seperate. Can this be done? Could someone please post an
example SQL statement demonstrating this if so?

You need a UNION statement or the equivalent. The syntax will depend on
what database server you are using. I'll assume MySQL, and since UNION
is only available from 4.0.0 onwards I'll assume you have that version.
The main requirement is that the columns of both tables have the same
names and the same types. You haven't given any DDL for the tables so
I'll make some up...

CREATE TABLE CommunityNews ( cnid INT, newsdate DATE, newstext
VARCHAR(1000) )
CREATE TABLE PressNews ( pnid INT, newsdate DATE, newstext
VARCHAR(1000) )

(SELECT newsdate, newstext, "1" AS source
FROM CommunityNews
WHERE newsdate >= '"20051107")
UNION
(SELECT newsdate, newstext, "2" AS source
FROM PressNews
WHERE newsdate >= "20051107")
ORDER BY newsdate DESC

Your result is now a mixture of rows from both tables. You can tell
which table a row came from by looking at the "source" column.

---
Steve

[bizt]

> Why don't you query both datasources seperately, and then in PHP decide

which one to show in your unified 'resultset' ?

Thanks for the suggestion but as well as querying tables one after the
other, wouldnt this also restrict me to outputing them one after the
other. I kinda need to integrate them and then probably order them by
date.

Anyway, I see I need to gain a little understanding of the UNION
command in SQL which is fine. I have tried visiting a couple of
websites and they suggest all selected columns need to be the same data
type. This presents two problems for me:

1) Some columns have different names. For example, both have a text
data type column containing the body text of each article but one is
called 'desctext' and the other 'article'. Could this be worked around
by using the AS command (ie. article AS desctext)?

2) The column that provides the entry date, 'createdon', there are two
data types between the tables. One is datetime (in the format
'2005-11-08 12:04:43' and the other timestamp ('2005-11-08
12:04:43+01'). Is this an issue?

Unfortunately both tables were created a year apart and when I started
working on the later table, this idea wasnt thought of until now.

Burnsy

[jerry gitomer]

bizt wrote:

Why don't you query both datasources seperately, and then in PHP decide
which one to show in your unified 'resultset' ?

Thanks for the suggestion but as well as querying tables one after the
other, wouldnt this also restrict me to outputing them one after the
other. I kinda need to integrate them and then probably order them by
date.

Anyway, I see I need to gain a little understanding of the UNION
command in SQL which is fine. I have tried visiting a couple of
websites and they suggest all selected columns need to be the same data
type. This presents two problems for me:

1) Some columns have different names. For example, both have a text
data type column containing the body text of each article but one is
called 'desctext' and the other 'article'. Could this be worked around
by using the AS command (ie. article AS desctext)?

This may be database dependent -- but I know from experience
that if you are using Oracle different column names are not a
problem.
2) The column that provides the entry date, 'createdon', there are two
data types between the tables. One is datetime (in the format
'2005-11-08 12:04:43' and the other timestamp ('2005-11-08
12:04:43+01'). Is this an issue?
Try it and see if it works. If it doesn't would it be feasible
to convert the datetime column to a timestamp?
Unfortunately both tables were created a year apart and when I started
working on the later table, this idea wasnt thought of until now.

Burnsy

[bizt]

Ok, tried a simple example:

$select = "SELECT title, desctext FROM commnews UNION SELECT title,
article AS desctext FROM pressnews ORDER BY createdon";

$result = pg_exec ($db, $select);
But giving me the following error:
Warning: pg_exec(): Query failed: ERROR: column "createdon" does not
exist in /data/httpd/VirtualHosts/web-sandbox/htdocs/_testing/union.php
on line 34
The column 'createdon' does exist in both so I can only assume that my
ORDER BY command isnt properly formated. Is this the correct way to
ORDER a UNION SELECT?

Cheers

Burnsy

[Tim Roberts]

"bizt" <bi******@yahoo.co.uk> wrote:

Ok, tried a simple example:

$select = "SELECT title, desctext FROM commnews UNION SELECT title,
article AS desctext FROM pressnews ORDER BY createdon";

$result = pg_exec ($db, $select);

But giving me the following error:

Warning: pg_exec(): Query failed: ERROR: column "createdon" does not
exist in /data/httpd/VirtualHosts/web-sandbox/htdocs/_testing/union.php
on line 34

The column 'createdon' does exist in both so I can only assume that my
ORDER BY command isnt properly formated. Is this the correct way to
ORDER a UNION SELECT?

Almost. The union-ed query does not have column names, so you have to
refer to them by number. That means the column must participate in the
query. This should work:

SELECT title, desctext, createdon FROM commnews
UNION
SELECT title, article, createdon FROM pressnews
ORDER BY 3;
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.