How can I prevent duplicate values in SQL?

Question

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

Could you expand a bit on what you want your output to look like? Right now I am a little confused as to what you mean by "prevent the cost of 89 from being duplicated". — jaredk
– jaredk, Commented Mar 10, 2014 at 12:55
Your cost_price is calculated, think about changing that. What is the relation between cost_price and serial_no you are looking for? — Moslem Ben Dhaou
– Moslem Ben Dhaou, Commented Mar 10, 2014 at 12:56
Would you like to get the aggregated cost_price? So that the system is giving you the sum of the cost_price? What would you like your query to return? — olovholm
– olovholm, Commented Mar 10, 2014 at 13:04
Perhaps it would be less misleading if you change the alias COST_PRICE in the result set to something like TOTAL_JOB_PRICE. As you say, "There is nothing wrong with the result." — Karl Kieninger
– Karl Kieninger, Commented Mar 10, 2014 at 13:04

srayner · Accepted Answer · 2014-03-10 13:45:22Z

1

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.

answered Mar 10, 2014 at 13:45

srayner

1,8396 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I prevent duplicate values in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related