GM-Script-Writer-62850 GM-Script-Writer-62850 -4 years ago 69
PHP Question

SQL JOIN with multiple rows conditionally

I have 2 tables in a SQLite database that I am accessing it via PHP

This is sample data, it is enough to describe what I want to do but not why.

Lets call these tables:

TableA
,
TableB
, and
TableC


------------------------
| Unique_ID | Name |
------------------------
| 1 | Sam |
------------------------
| 2 | Bob |
------------------------
| 3 | Jill |
------------------------

*Assume more rows for other possible foreign keys below
--------------------------------------------
| Unique_ID | Foreign_Key | Time_Stamp |
--------------------------------------------
| 1 | 1 | 1495298339 |
--------------------------------------------
| 2 | 1 | 1495298350 |
--------------------------------------------
| 3 | 1 | 1495298365 |
--------------------------------------------


What I want in my output would be something line this

------------------------------------
| Name | First_Time | Last_Time |
------------------------------------
| Sam | 1495298339 | 1495298365 |
------------------------------------
| Jill | 1495298787 | 1495298805 |
------------------------------------


Basically I want to pull the fist and last time stamp into the 1st table, but I am not sure how to do a join line this or if it is even possible, multi-layered select queries with limited results of 1?


I am hoping there is a way to avoid looping through each value in
TableA
and making two separate queries to get the first (lowest) and last (highest)
Time_Stamp
in
TableB

Answer Source

You can do a join between the first table and a processed version of the second table. The second table is a group by using the Foreign_Key, and the computed values are the minimum and maximum of Time_Stamp. Something like this:

select A.Unique_Id, B.First_Time, B.Last_Time
from TableA as A
join (
    select Foreign_Key, min(Time_Stamp) as First_Time, max(Time_Stamp) as Last_Time
    from Table B
    group by Foreign_Key
) as B
on A.Unique_Id = B.Foreign_Key
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download