locked
The view should give me 100 results when I execute it but it keeps giving me repeats and 1000 results, I'm trying to use distinct but it isnt working either. RRS feed

  • Question

  • User-1617803953 posted

    SELECT DISTINCT job_id, jobdate_time, accountant.staff_id, accountant.first_name + ' ' + accountant.last_name AS AccountantName, client.first_name + ' ' + client.last_name AS ClientName, job_type.jobtype_id, jobtype_name, jobduration_minutes, jobduration_minutes/15*costper_15min AS JobCost, client_pay
    FROM job JOIN accountant
    ON job.staff_id = accountant.staff_id
    JOIN job_type
    ON job_id = job.job_id
    JOIN client
    ON job_type.jobtype_id = client_id

    Sunday, October 27, 2019 3:01 AM

All replies

  • User-719153870 posted

    Hi Console13,

    This should be your table structure related issue, with only the query the community won't be able to help.

    Maybe you can try to use the LEFT JOIN instead of JOIN, JOIN with no other identification stands for INNER JOIN.

    Or, you can try to generate your database or related tables to scipts and share to see if we can provide more help.

    Best Regard,

    Yang Shen

    Monday, October 28, 2019 5:31 AM
  • User-1780421697 posted

    You need to find where is exact problem, 

    Removing Join one by one and analyze the result, once you found the table which is causing issue then you can find some solution by
    managing data in temporary table and then join it or any other way.

    SELECT DISTINCT job_id, jobdate_time, accountant.staff_id, accountant.first_name + ' ' + accountant.last_name AS AccountantName, client.first_name + ' ' + client.last_name AS ClientName, job_type.jobtype_id, jobtype_name, jobduration_minutes, jobduration_minutes/15*costper_15min AS JobCost, client_pay
    FROM job JOIN accountant
    --ON job.staff_id = accountant.staff_id
    --JOIN job_type
    --ON job_id = job.job_id
    --JOIN client
    --ON job_type.jobtype_id = client_id

    SELECT DISTINCT job_id, jobdate_time, accountant.staff_id, accountant.first_name + ' ' + accountant.last_name AS AccountantName, client.first_name + ' ' + client.last_name AS ClientName, job_type.jobtype_id, jobtype_name, jobduration_minutes, jobduration_minutes/15*costper_15min AS JobCost, client_pay
    FROM job JOIN accountant
    ON job.staff_id = accountant.staff_id
    JOIN job_type
    --ON job_id = job.job_id
    --JOIN client
    --ON job_type.jobtype_id = client_i

    ...so on

    Tuesday, October 29, 2019 1:04 PM
  • User-37275327 posted

    Suspecting on...if job_id is input parameter, move it to where clause.

    ON job_id = job.job_id

    Wednesday, October 30, 2019 6:36 AM
  • User1408931231 posted

    I would suggest the same. Start with your base query that gives you the 100 records you are looking for. Then start with LEFT joins from there. 

    Thursday, October 31, 2019 7:02 PM