Retrieving info from 2 tbls & ordering it

Hi

I have 2tables. 1 for incoming & the other for outgoing messages. They
both have columns for the userid & datetime_received/sent. I'd like to
retrieve all records from both tables for a specific user id & order all
the records returned by the two datetime_received/sent fields. Is this
possible & if so could someone help me out as to how I could achieve this.

Kind regards
Warren

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Retrieving info from 2 tbls & ordering it

--00151774051ca5a65f04743fb778
Content-Type: text/plain; charset=ISO-8859-1

I think that you can do what you want with a simple union query:
select * from incoming where user_id = 123
union
select * from outgoing where user_id = 123
order by datetime_received, datetime_sent

hth,
Arthur

On Wed, Sep 23, 2009 at 10:02 AM, Warren Windvogel wrote:

> Hi
>
> I have 2tables. 1 for incoming & the other for outgoing messages. They both
> have columns for the userid & datetime_received/sent. I'd like to retrieve
> all records from both tables for a specific user id & order all the records
> returned by the two datetime_received/sent fields. Is this possible & if so
> could someone help me out as to how I could achieve this.
>
> Kind regards
> Warren
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=fuller.artful@gmail.com
>
>


--
Semi-retired SQL guru, interested in interesting projects not YAFOES (yet
another friendly order entry system).

--00151774051ca5a65f04743fb778--

RE: Retrieving info from 2 tbls & ordering it

You should drop the 'datetime_sent' off that order by clause, it will return
an error.

Should be

select * from incoming where user_id = 123
union
select * from outgoing where user_id = 123
order by datetime_received;

Always use the column names in the first select for ordering union queries.


John Daisley
MySQL & Cognos Contractor

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer (CMDEV)
IBM Cognos BI Developer

Telephone +44 (0)7812 451238
Email john@butterflysystems.co.uk

-----Original Message-----
From: Arthur Fuller [mailto:fuller.artful@gmail.com]
Sent: 23 September 2009 15:42
To: mysql@lists.mysql.com
Subject: Re: Retrieving info from 2 tbls & ordering it

I think that you can do what you want with a simple union query:
select * from incoming where user_id = 123
union
select * from outgoing where user_id = 123
order by datetime_received, datetime_sent

hth,
Arthur

On Wed, Sep 23, 2009 at 10:02 AM, Warren Windvogel
wrote:

> Hi
>
> I have 2tables. 1 for incoming & the other for outgoing messages. They
both
> have columns for the userid & datetime_received/sent. I'd like to retrieve
> all records from both tables for a specific user id & order all the
records
> returned by the two datetime_received/sent fields. Is this possible & if
so
> could someone help me out as to how I could achieve this.
>
> Kind regards
> Warren
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=fuller.artful@gmail.com
>
>


--
Semi-retired SQL guru, interested in interesting projects not YAFOES (yet
another friendly order entry system).

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.409 / Virus Database: 270.13.112/2390 - Release Date: 09/23/09
05:52:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org