Combining two queries on the same table

[autoalert]

HI

I am running mysql 5.0.51a and have a table with the following structure

ID, Date, startTime, stopTime, Name

I want to find the last stopTime from yesterday for each name and the first startTime from today for each name.

I have tried using union but end up with a stopTmes for today and yesterday as well as the startTimes for today and yesterday for each name. I also tried using sub queries but get an error as there is more than one result for each query.

How can I create a query to just get the the yesterdays stop time and todays start time only for each name.

Thanks in advance for your help.
Regards
Richard

[amitpatel66]

Could you please post what you have tried so far?

[autoalert]

Could you please post what you have tried so far?

Sorry, I tried

SELECT
(SELECT MAX(stopTime) FROM `aaworkingdetailsbreakdown` WHERE `work_date` = (curdate()-1)),
(SELECT MIN(startTime) FROM `aaworkingdetailsbreakdown` WHERE `work_date` = (curdate())),
`name` from `aaworkingdetailsbreakdown` WHERE `customer_id` = 55 group by `name`

this is the closest I got but just shows the max stop and min start. If I try adding the group by name within each select statement
I get the error #1242 - Subquery returns more than 1 row

I also tried using the following union query which showed the data I wanted but doubled up the results as it showed both the start and stop times for each name for each day.

(SELECT MAX(stopTime),MIN(startTime),`name`
FROM `aaworkingdetailsbreakdown`
WHERE `work_date` = (curdate()-1) and `customer_id` = 55
group by `name`)
UNION
(SELECT MAX(stopTime),MIN(startTime),`name`
FROM `aaworkingdetailsbreakdown`
WHERE `work_date` = (curdate()) and `customer_id` = 55
group by `name`)

[coolsti]

You need something like the query technique that I explained in this thread:

http://bytes.com/forum/thread819577.html

Extending this to your problem, I wonder if this would work? I never tried it with a union before.

(SELECT T1.id, T1.stopTime FROM aaworkingdetailsbreakdown T1 left join
(SELECT T2.id, max(T2.stopTime) as maxstoptime
FROM aaworkingdetailsbreakdown T2 where work_date=(curdate()-1) group by T2.id) T3
on (T1.id = T3.id and T1.stopTime = t3.maxstoptime) where T1.work_date=(curdate()-1)
UNION
(SELECT T4.id, T4.startTime FROM aaworkingdetailsbreakdown T4 left join
(SELECT T5.id, min(T5.startTime) as minstarttime
FROM aaworkingdetailsbreakdown T5 where work_date=(curdate()) group by T5.id) T6
on (T4.id = T6.id and T4.startTime = t6.minstarttime) where T4.work_date=(curdate())

[autoalert]

Thanks for this and for the other thread

Unfortunately I got an error until I changed (T1.id = T3.id and T1.stopTime = t3.maxstoptime) to (T1.id = T3.id and T1.stopTime = maxstoptime) although I'm hoping that didn't undermine the crux at what you were saying.

This worked but provided similar results to what I was getting previously. With the union it only shows the columns in the first select statement so I only got the stopTimes from yesterday. Im not sure exactly how to add in the startTimes but I think if I did I would then get start and stop times for both days again.

I tried using subqueries rather unions as below, but then got the error #1241 - Operand should contain 1 column(s)

SELECT
(
SELECT T1.id, T1.stopTime, T1.name
FROM aaworkingdetailsbreakdown T1
left join
(

SELECT T2.id, max(T2.stopTime) as maxstoptime
FROM aaworkingdetailsbreakdown T2
where work_date=(curdate()-1)
group by T2.id
)
T3 on (T1.id = T3.id
and T1.stopTime = maxstoptime)
where T1.work_date=(curdate()-1)
)
,
(
SELECT T4.id, T4.startTime, T4.name
FROM aaworkingdetailsbreakdown T4
left join
(
SELECT T5.id, min(T5.startTime) as minstarttime
FROM aaworkingdetailsbreakdown T5
where work_date=(curdate())
group by T5.id
)
T6 on (T4.id = T6.id
and T4.startTime = minstarttime)
where T4.work_date=(curdate())
)
,
name from aaworkingdetailsbreakdown group by name

[coolsti]

Hi!

Yes, you found a typo in the example I made for you. It should have worked if you changed the t3 to T3, because MySql is case sensitive. It also works for you by removing the alias T3 altogether, apparently in this case which table was meant was clear to MySql.

I do not understand why the Union would not work for you. Perhaps you should try the second part of the query (for the start times) by itself without the first part and without the Union, to make sure that you actually do get some selected rows. Perhaps there is something wrong with the data in your table, or with the query (another typo?) that causes the select after the union to return nothing. Try to debug this first, as it should work.

Oh and yes, there is a second typo in my example!!! Make sure you change the t6 to T6 in the second part!!!

Actually, on second thought, try this:

(SELECT T1.id, T1.stopTime as time, 'stoptime' as label FROM aaworkingdetailsbreakdown T1 left join
(SELECT T2.id, max(T2.stopTime) as maxstoptime
FROM aaworkingdetailsbreakdown T2 where work_date=(curdate()-1) group by T2.id) T3
on (T1.id = T3.id and T1.time = T3.maxstoptime) where T1.work_date=(curdate()-1)
UNION
(SELECT T4.id, T4.startTime as time, 'starttime' as label FROM aaworkingdetailsbreakdown T4 left join
(SELECT T5.id, min(T5.startTime) as minstarttime
FROM aaworkingdetailsbreakdown T5 where work_date=(curdate()) group by T5.id) T6
on (T4.id = T6.id and T4.time = T6.minstarttime) where T4.work_date=(curdate())

The changes, besides me fixing the two typos are the following:

I gave the alias of "time" to both stoptime in the first query and starttime in the second query. This is because you are making a UNION of the two queries, and so the column names maybe have to be identical. In order to be able to tell one from the other, I also added a third "column" which is just the string, either "stoptime" or "starttime", and I give this the name "label".

See if that works for you.

[autoalert]

Thanks for all your help with this. There was a bracket missing which I added in hopefully in the right place but I still got a mysql error #1064.

However, I managed to resolve the query by creating a view for all the stopTimes from yesterday and then another view for all the startTimes for today. Then it was simply a matter of selecting all from the two "tables" and joining on ID.

Thanks once again for your help.
Regards
Richard