CODEWITHSUNDEEP
sqlpostgresql
eixcs
eixcs

Reputation: 1989

Multiple row results when selecting with join

I'm trying to select a user's name to go with my other data, but I keep getting multiple rows with my SELECT query.

Name field is broken, but the others work fine.

Struckture goes as follows- user has work_detail_id and shift_worker has work_detail_id. I need the name of user that has the same work_detail_id as the shift_worker (shift_worker > work_detail > user).

How would I fix this?

    SELECT
  (SELECT name
   FROM user_acc
   JOIN shift_worker ON user_acc.work_detail_id = shift_worker.work_detail_id
   WHERE user_acc.work_detail_id = shift_worker.work_detail_id
   GROUP BY user_acc.name) AS name,
       count(sw.id) AS shift_count,
       sum(sw.actual_work_duration) AS work_duration,
       sum(sw.actual_shift_duration) AS shift_duration
FROM shift_worker sw
JOIN work_detail wd ON sw.work_detail_id = wd.id
WHERE sw.work_detail_id = wd.id
  AND sw.actual_work_start_time > '2019-10-01 00:00:00'
  AND sw.actual_work_start_time < '2019-10-31 00:00:00'
GROUP BY wd.id

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Your query is a mess. For instance, it repeats conditions in the on clause and the where clause. It is using subqueries unnecessarily.

I think you want:

SELECT ua.name as name,
       count(*) as shift_count,
       sum(sw.actual_work_duration) as work_duration,
       sum(sw.actual_shift_duration) as shift_duration
FROM shift_worker sw JOIN
     user_acc ua
     ON ua.work_detail_id = sw.work_detail_id JOIN
     work_detail wd
     ON sw.work_detail_id = wd.id
WHERE sw.actual_work_start_time > '2019-10-01' AND
      sw.actual_work_start_time < '2019-10-31'
GROUP BY sw.id, ua.name;

I also suspect that you want the entire month of October. If so:

WHERE sw.actual_work_start_time >= '2019-10-01' AND
      sw.actual_work_start_time < '2019-11-01'

Upvotes: 1

DatabaseCoder
DatabaseCoder

Reputation: 2032

Try 2 -

SELECT
    max(ua.name) as name,
    count(sw.id) as shift_count,
    sum(sw.actual_work_duration) as work_duration,
    sum(sw.actual_shift_duration) as shift_duration
FROM shift_worker sw
JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_id
JOIN work_detail wd ON sw.work_detail_id = wd.id
WHERE sw.work_detail_id = wd.id AND
    sw.actual_work_start_time > '2019-10-01 00:00:00' AND
    sw.actual_work_start_time < '2019-10-31 00:00:00'
GROUP BY wd.id

Try 1-

I think you need co-related query instead of sub-query. I have removed extra join with shift_worker table from sub-query. Please try this-

SELECT
    (
        SELECT name 
        FROM user_acc
        WHERE user_acc.work_detail_id = sw.work_detail_id 
        GROUP BY user_acc.name
    ) as name, 
    count(sw.id) as shift_count, 
    sum(sw.actual_work_duration) as work_duration, 
    sum(sw.actual_shift_duration) as shift_duration  
FROM shift_worker sw 
JOIN work_detail wd ON sw.work_detail_id = wd.id 
WHERE sw.work_detail_id = wd.id AND 
    sw.actual_work_start_time > '2019-10-01 00:00:00' AND 
    sw.actual_work_start_time < '2019-10-31 00:00:00' 
GROUP BY wd.id

Upvotes: 1

Related Questions