CODEWITHSUNDEEP
sql
srayner
srayner

Reputation: 1839

How can I prevent duplicate values in SQL?

I can't think of a good title for this question but here goes..

I have this SQL query

SELECT
  J.SRV_JOB_ID,
  C.UNIT_COST * C.QTY AS COST_PRICE,
  E.SERIAL_NO
FROM
  SRV_JOB J
  LEFT JOIN SRV_JOB_COST C       ON C.SRV_JOB_ID       = J.SRV_JOB_ID
  LEFT JOIN SRV_JOB_EQUIPMENT JE ON JE.SRV_JOB_ID      = J.SRV_JOB_ID
  LEFT JOIN SRV_EQUIPMENT E      ON E.SRV_EQUIPMENT_ID = JE.SRV_EQUIPMENT_ID
WHERE
  j.srv_job_id = 52423

which is somewhat simplified for the purpose of the question, and gives these results;

srv_job_id  cost_price serial_no
52423       89         400887
52423       89         400888
52423       89         400889

because there is one job with an id of 52423 and a cost of 89, but there are three associated serial numbers.

There is nothing wrong with the result, but it is misleading because it looks like each serial number has a cost of 89, when in fact the total cost for all three is 89.

How can I prevent the cost of 89 being duplicated? I can't change the database schema, but i can change the query.

The result i would like would be

srv_job_id  cost_price serial_no
52423       89         400887
52423       null       400888
52423       null       400889

Upvotes: 0

Views: 80

Answers (1)

srayner
srayner

Reputation: 1839

I've broken it into two separate queries. One to list the job(s) details and one to list the serial numbers for each job.

Thanks for you help. Your comments led me to thinking about the problem differently.

Upvotes: 1

Related Questions