From: Peter Brawley
Date: October 7 2005 7:28pm
Subject: Re: Joining tables, duplicating none
List-Archive: http://lists.mysql.com/mysql/190115
Message-Id: <4346CC60.6020105@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jonathan,

 >I have two tables with date and uid cols. in common.
 >Table 1 has one row per date, Table 2 has a maximum
 >of 7 rows per date.

 >select t1.date, t1.val, t2.val from t1
 >right join t2 on t1.date = t2.date
 >where t1.date between '2005-08-01' and '2005-08-14'
 >and t1.uid = 'me';

A right join returns rows from t2 whether or not there are matches in 
t1. Is that what you want?

For examples of turning query result rows into columns see
   pivot table schedule
   pivot table using join
   rows into columns
at http://www.artfulsoftware.com/queries.php. 

PB

-----

Jonathan Mangin wrote:

> I have two tables with date and uid cols. in common.
> Table 1 has one row per date, Table 2 has a maximum
> of 7 rows per date.
>
> select t1.date, t1.val, t2.val from t1
> right join t2 on t1.date = t2.date
> where t1.date between '2005-08-01' and '2005-08-14'
> and t1.uid = 'me';
>
> +------------------------------+
> | t1.date    | t1.val | t2.val |
> +------------+--------+--------+
> | 2005-08-01 |     92 |   18.3 |
> | 2005-08-01 |     92 |   23.3 |
> +------------+--------+--------+
>
> Is there any way to get something like this
>
> +------------------------------+--------+
> | t1.date    | t1.val | t2.val | t2.val |
> +------------+--------+--------+--------+
> | 2005-08-01 |     92 |   18.3 |   23.3 |
> +------------+--------+--------+--------+
>
> instead of duplicating Table 1 rows for every row
> in Table 2?
>
> Thanks,
> Jon
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 10/6/2005