469,900 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,900 developers. It's quick & easy.

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

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

Nov 7 '05 #1
7 9568
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
Nov 7 '05 #2
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
Nov 7 '05 #3
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

Nov 7 '05 #4
> 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

Nov 8 '05 #5
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

Nov 8 '05 #6
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

Nov 10 '05 #7
"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.
Nov 11 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by VisionSet | last post: by
8 posts views Thread by Rigga | last post: by
3 posts views Thread by Michael Thomas | last post: by
11 posts views Thread by dskillingstad | last post: by
10 posts views Thread by Jim Devenish | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.