Need remove duplicate rows

I have an sql statement that gets repair orders and repair visits, howver i only want one particualr visit being pulled out but its pulling them all out and then duplicating the parts ordered, and i dont know what im doing wrong im sure there is really simple way round this but i dont know what is wrong with me today just cant see the obvious i dont think.

Below example data i just want to be able to see engineer 1 and the parts ordered.

Tables used are

Tbl_property - just to get address etc
tbl_repair_order - order raised
tbl_repair_visit - all visits logged again order, this where both engineer names stored
tbl_partorder - where parts are linked to order

Example data:

Orderid: 123 - Engineer1 - Part 1
Orderid: 123 - Engineer1 - Part 2
Orderid: 123 - Engineer2 - part 1
Orderid: 123 - Engineer2 - part 2

SELECT dbo.tbl_repair_order.CONTRACT, dbo.tbl_property.PROPADDRESS, dbo.tbl_property.PROPNUMBER, dbo.tbl_property.FULLADDRESS, dbo.tbl_repair_order.ORDERID, dbo.tbl_repair_order.THEIRREF, dbo.tbl_repair_order.ORIGIN, dbo.tbl_repair_order.RAISEDDATE AS JOBRAISED, dbo.tbl_repair_order.ENGINEER AS ENG1, dbo.tbl_repair_visit.ENGINEER, dbo.tbl_partorder.EXSUPPLIERORDERID, dbo.tbl_partorder.SUPPLIER, dbo.tbl_partorder.RAISED AS PARTRAISED, dbo.tbl_partorder.CODE, dbo.tbl_partorder.ITEM, dbo.tbl_partorder.UNITCOST, dbo.tbl_partorder.QUANTITY, dbo.tbl_partorder.COST, dbo.tbl_partorder.EXPECTED, dbo.tbl_repair_visit.Status FROM dbo.tbl_property INNER JOIN dbo.tbl_repair_order ON dbo.tbl_property.PROPREF = dbo.tbl_repair_order.PROPREF INNER JOIN dbo.tbl_partorder ON dbo.tbl_repair_order.ORDERID = dbo.tbl_partorder.ORDERID LEFT OUTER JOIN dbo.tbl_repair_visit ON dbo.tbl_partorder.ORDERID = dbo.tbl_repair_visit.ORDERID WHERE (dbo.tbl_repair_order.CONTRACT = 'HOM') AND (dbo.tbl_repair_order.ORDERID = 4342) AND (dbo.tbl_repair_visit.Status = 'Parts Req')

no i dont want both the engineer details just the one from the visit table where the status was set to parts req as this is the negineer that ordered the parts?

2009-07-31 10:05:31

Please provide the table structures.

astander 2009-07-31 10:07:23

This is how they are linked, is that what you meanFROM dbo.tbl_property INNER JOIN dbo.tbl_repair_order ON dbo.tbl_property.PROPREF = dbo.tbl_repair_order.PROPREF INNER JOIN dbo.tbl_partorder ON dbo.tbl_repair_order.ORDERID = dbo.tbl_partorder.ORDERID LEFT OUTER JOIN dbo.tbl_repair_visit ON dbo.tbl_partorder.ORDERID = dbo.tbl_repair_visit.ORDERID

2009-07-31 10:24:43

the visit can be linked on the order table or the part table as orderid is in both i have played with few different ways linking

2009-07-31 10:25:26

I would need to see what the table structure look like, but from the given data provided, the specific order id would bring back rows for engineer1 part1 and 2 and engineer2 part1 and 2, which would seem like duplicates, unless this is what is required. you need to specify the engineerID aswell

astander 2009-07-31 10:29:35

Oh i think i found the issue, both visits are marked as parts req!!!!! Will have find another way round this now!!!!

2009-07-31 10:35:52

Advice: show the contents of the tables themselves.

Dems 2009-07-31 11:05:07

not sure what you mean by that sorry?

2009-07-31 11:14:33

what are the fields in the tables, and examples of the data itself? Not just the sql-query, the table definition and example data...

Dems 2009-07-31 23:00:20

ansaurus

tags:

views:

answers:

Need remove duplicate rows

related questions