Pawl <[email protected]> wrote:
>
> I need to joint two table
>
> This table show change set up limts value.
>
> +------------+-----------+-----------+
>> edittime | lim_lower | lim_upper |
> +------------+-----------+-----------+
>> 01.01.2011 | 0.8 | 12.1 |
> +------------+-----------+-----------+
>> 05.01.2011 | 0.7 | 12.1 |
> +------------+-----------+-----------+
>> 11.01.2011 | 0.1 | 11.8 |
> +------------+-----------+-----------+
>
>
> This is measure value.
> +------------+-----------+-----------+
>> startttime | value | operatorid|
> +------------+-----------+-----------+
>> 01.01.2011 | 7.7 | 1 |
> +------------+-----------+-----------+
>> 02.01.2011 | 17.1 | 1 |
> +------------+-----------+-----------+
>> 03.01.2011 | 6.2 | 1 |
> +------------+-----------+-----------+
>> 04.01.2011 | 7.7 | 1 |
> +------------+-----------+-----------+
>> 05.01.2011 | 17.1 | 1 |
> +------------+-----------+-----------+
>> 06.01.2011 | 6.2 | 1 |
> +------------+-----------+-----------+
>> 09.01.2011 | 7.7 | 1 |
> +------------+-----------+-----------+
>> 10.01.2011 | 12.1 | 1 |
> +------------+-----------+-----------+
>> 15.01.2011 | 6.7 | 1 |
> +------------+-----------+-----------+
>
> I need make summary table like this:
>
> +------------+-------+-----------+----------+-----------+
>> startttime | value | edittime |lim_lower | lim_upper |
> +------------+-------+-----------+----------+-----------+
>> 01.01.2011 | 7.7 | 01.01.2011| 0.8 | 12.1 |
> +------------+-------+-----------+----------+-----------+
>> 02.01.2011 | 17.1 | 01.01.2011| 0.8 | 12.1 |
> +------------+-------+-----------+----------+-----------+
>> 03.01.2011 | 6.2 | 01.01.2011| 0.8 | 12.1 |
> +------------+-------+-----------+----------+-----------+
>> 04.01.2011 | 7.7 | 01.01.2011| 0.8 | 12.1 |
> +------------+-------+-----------+----------+-----------+
>> 05.01.2011 | 7.7 | 01.05.2011| 0.7 | 12.1 |
> +------------+-------+-----------+----------+-----------+
> ...
>
> I tried to use cmd JOIN in this content, but didn't work it.
>
> SELECT JX.*,JX_lim.* FROM JX JOIN JX_lim ON JX_lim.edittime <= JX.starttime
You probably want something like
select * from JX, JX_lim lim1 where lim1.rowid = (
select rowid from JX_lim lim2
where lim2.edittime <= JX.starttime
order by lim2.edittime desc limit 1
);
But first you'd need to address the issue with data format that Simon pointed
out. The way you have it, comparisons won't work right.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
