CODEWITHSUNDEEP
sqlsql-serverloopssubquery
Ahmed
Ahmed

Reputation: 655

Using some sort of loop in a subquery of SQL

I am making an SQL query in which I want to get some results for each latest entry of user, to do this I have made this query.

SELECT lA.attendance_id, lA.time_in, lA.time_out, lS.title, lS.end_time, lU.uid
FROM logi_attendance AS lA
INNER JOIN
    logi_users AS lU ON lA.user_id = lU.user_id
    INNER JOIN
        logi_shifts AS lS ON lU.shift_id = lS.shift_id
WHERE
(lA.time_in IS NOT NULL) AND
(lA.time_out IS NULL) AND
(lA.attendance_id =
    (
    SELECT TOP (1) A.attendance_id
    FROM logi_attendance AS A
    INNER JOIN
        logi_users AS B ON A.user_id = B.user_id
        ORDER BY A.attendance_id DESC
    )
)

As you can see I have used a subquery to retrieve the latest records of each user (new records are created on daily basis) so getting the latest record is requirement. Now see that I have used TOP(1) in subquery which restrict it to return 1 record only. This allows the main query to run successfully but it is not useful because it only returns record of one user which it finds first. But I want to get results of all users, but it should be done in a way that subquery returns each latest id one by one so the main query executes successfully. Right now if I remove TOP(1) then it gives me an error that subquery returns more than 1 value which is incorrect.

I don't know if I explained it properly, let me know if you don't understand what I am trying to ask and I will try to explain better.

Thanks.

Upvotes: 0

Views: 2740

Answers (3)

A-K
A-K

Reputation: 17080

Your subquery is not correlated. You need a correlated subquery, as follows:

SELECT lA.attendance_id, lA.time_in, lA.time_out, lS.title, lS.end_time, lU.uid
FROM logi_attendance AS lA
INNER JOIN
    logi_users AS lU ON lA.user_id = lU.user_id
    INNER JOIN
        logi_shifts AS lS ON lU.shift_id = lS.shift_id
WHERE
(lA.time_in IS NOT NULL) AND
(lA.time_out IS NULL) AND
(lA.attendance_id =
    (
    SELECT TOP (1) A.attendance_id
    FROM logi_attendance AS A
    INNER JOIN
        logi_users AS B ON A.user_id = B.user_id
--make it correlated:
    WHERE A.user_id = lU.user_id
        ORDER BY A.attendance_id DESC
    )
)

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55524

You should be able to add

WHERE A.user_id = lA.user_id

to your sub-query. This would get you the most recent entry per user.

Upvotes: 1

K. Bob
K. Bob

Reputation: 2688

Not 100% sure of a solution but the reason it needs the TOP 1 is because you test lA.attendance_id = something and that something has to only be 1 result.

Try changing it to lA.attendance_id IN (select xxxxx) and that should get you more results.

Upvotes: 0

Related Questions