Bdtjtk

I need to combine the following three SELECT statements into a single SQL Statement:

1. SELECT TotalTime FROM Session WHERE device_uid = ${my_uid} ORDER BY StartTime DESC LIMIT 1;



2. SELECT HoursAtService FROM Service WHERE device_uid = ${my_uid} ORDER BY StartTime DESC LIMIT 1;



3. SELECT ServiceHours FROM Type WHERE type_uid = ${my_type_uid};

Preferably, I want to get my result as a single row with the following fields:

Result:

----------------------------------------------------------------

uid, TotalTime, HoursAtService, ServiceHours

I'm thinking that I probably need to do an inner join, but I'm not 100% sure how to approach it. Something like:

SELECT uid, TotalTime, HoursAtService, ServiceHours FROM Device AS t1 INNER JOIN (... -> continues

Doing a join between tables is easy enough, but how do you approach doing the join with multiple WHERE clauses for the various tables?

Could someone show me how I would structure the query?

EDIT - Table Schemas

Device:

---------------------

device_uid, type_uid, ....



Session:

---------------------

device_uid, session_uid, TotalTime, StartTime, EndTime, SessionTime



Service:

---------------------

device_uid, service_uid, StartTime, EndTime, HoursAtService



Type:

---------------------

type_uid, Name, ServiceHours

You can merge above single queries in one query like below MYSQL Query:

SELECT Session.uid, Session.TotalTime, Service.HoursAtService, ServiceHours.ServiceHours

FROM Session 

JOIN Service ON Service.udi = Session.uid

JOIN ServiceHours ON ServiceHours.type_uid = Session.uid

WHERE Session.uid = ${my_uid}

ORDER BY Session.StartTime DESC LIMIT 1; 

Change the column names as you have in your related tables.

You can use the below query to achieve the things which you mentioned, I have also taken care of square brackets for the keywords because service and session are SQL keywords.

SELECT 

    [Device].Type_uid AS DeviceTypeId, [Device].Device_uid AS DeviceId, [Session].TotalTime, [Service].HoursAtService, [Type].ServiceHours 

FROM 

    [Session] INNER JOIN [Device] ON [Session].Device_uid = [Device].Device_uid 

    [Service] INNER JOIN [Device] ON [Service].Device_uid = [Device].Device_uid

    [Type] INNER JOIN [Device] ON [Type].Type_uid = [Device].Type_uid

WHERE

    [Device].Type_uid = ${my_type_uid} AND [Device].Device_uid = ${my_uid}

Try something like this:

SELECT ${my_uid} AS uid,

       (SELECT TotalTime FROM Session WHERE uid = ${my_uid} ORDER BY StartTime DESC LIMIT 1) AS TotalTime,

       (SELECT HoursAtService FROM Service WHERE udi = ${my_uid} ORDER BY StartTime DESC LIMIT 1) AS HoursAtService,

       (SELECT ServiceHours FROM Type WHERE type_uid = ${my_type_uid) AS ServiceHours