Sql Query

Hi,
Can you explain your question better ?
Thanks.

Hello John & Albert,


Thanks for your comments, yes I think i was not descriptive in my question sorry for that, actually the follwing sql

Select e.eqpt_id,
       e.eqpt_name,
       t.tech_name,
       s.part_name
from   equipment_master e,
       tech_master t,
       spare_parts s
where  e.eqpt_id = t.eqpt_id and
       e.eqpt_id = s.eqpt_id
where I want to avoid cartesian.

The scenario is like this, One equipment is maintained by two technician and they have used two spare parts.

but the query result show that the spare used are four instead two only (repeating the same two spares). How to avoid this ?

Thanks once again for your time.

first of all you might try the select statement:

SELECT DISTINCT e.eqpt_id, e.eqpt_name, t.tech_name, s.part_name

tell me if this works

Hi

Sorry, but I disagree with the use of the DISTINCT clause "osbjmq".  It might give you what you want IN THIS CASE, but it masks a more fundamental problem with the data or in the design of the tables.

Taking a quick look at the comments without having access to the structures of the 3 tables involved, I would hazard a guess that the SPARE_PARTS table might have a composite primary key which contains BOTH the key of EQUIP_MASTER and TECH_MASTER ?

Please post the table structures (significant columns only if you like) and show the primary keys on each table.

I think this might help shed more light on the problem.

Cheers

Hello!!
Pls find the table structures
Table 1
Job_order_Number         PK
Eqpt_id
Eqpt_name
Maintenance_date


Record

M10001,EQP-1002,GENERATOR,10-NOV-2002

Table 2
Job_order_number    FK
Tech_id
Tech_name
Man_hours
Labor_rate

EX. DATA
M10001,1020,VVVVVVV,20,45
M10001,1022,TTTTTTT,20,45



Table 3 [detail table]
Job_order_number    FK
Spare_id
Spare_name
Qty
unit_cost

EX. DATA
M10001,DE12304,UUUUUUUUUUUUUU,2,345.00
M10001,DF12349,JJJJJJJJJJJJJJ,2,524.00


The expected result should be as  follows;

M10001,VVVVVVV,900,UUUUUUUUUUUUUU,690.00
M10001,TTTTTTT,900,JJJJJJJJJJJJJJ,1048.00

But the result we get is
M10001,VVVVVVV,1800,UUUUUUUUUUUUUU,690.00
M10001,TTTTTTT,1800,JJJJJJJJJJJJJJ,1048.00


Hope this is would be much clearer in my question.

Thanks once again for all valuable suggestion.

Regards

Hi

The reason you are getting twice as many records as you want is because there are actually 2 separate parts to the question you are trying to answer as 1 question.

1 - Who has worked on this equipment and how many hours have they spent and what is their total cost?
2 - How many spare parts have been used for this equipment and what is the total cost?

Results of queries:

1 - M10001,VVVVVVV,900
    M10001,TTTTTTT,900

2 - M10001,UUUUUUUUUUUUUU,690.00
    M10001,JJJJJJJJJJJJJJ,1048.00

If you combine them as you seem to want to, you get the output as follows:

M10001,VVVVVVV,900,UUUUUUUUUUUUUU,690.00
M10001,TTTTTTT,900,UUUUUUUUUUUUUU,690.00
M10001,VVVVVVV,900,JJJJJJJJJJJJJJ,1048.00
M10001,TTTTTTT,900,JJJJJJJJJJJJJJ,1048.00

The output you want to achieve is as follows:

M10001,VVVVVVV,900,UUUUUUUUUUUUUU,690.00
M10001,TTTTTTT,900,JJJJJJJJJJJJJJ,1048.00

If I was asked to interpret this output I would say that Technician VVVVVVV has worked on equipment M10001 and has used (or fitted) spare part UUUUUUUUUUUUUU to it, and that Technician TTTTTTT has worked on equipment M10001 and has used (or fitted) spare part JJJJJJJJJJJJJJ to it.

In other words there is an implied relationship between the Technician and the spare part in the output.  There may be a way to get the output you want in the way you want it but I would doubt the usefulness of the data output in this way.

If I was asked to provide this kind of information using a single query, the only way in which I can think of trying to provide a meaningful answer would be to provide the following information:

Equip Id Equip Name Total Labour Cost Total Parts Cost
-------- ---------- ----------------- ----------------
M10001   GENERATOR               1800          1738.00

If you need to find out which parts or which technician had worked on the equipment, this could be provided as 2 separate queries.

I think that you should re-analyse your requirement to display the data in this way and try to come up with a more meaningful way of showing the relationships between the 3 tables.

HTH

Mark

Hi

The reason you are getting twice as many records as you want is because there are actually 2 separate parts to the question you are trying to answer as 1 question.

1 - Who has worked on this equipment and how many hours have they spent and what is their total cost?
2 - How many spare parts have been used for this equipment and what is the total cost?

Results of queries:

1 - M10001,VVVVVVV,900
    M10001,TTTTTTT,900

2 - M10001,UUUUUUUUUUUUUU,690.00
    M10001,JJJJJJJJJJJJJJ,1048.00

If you combine them as you seem to want to, you get the output as follows:

M10001,VVVVVVV,900,UUUUUUUUUUUUUU,690.00
M10001,TTTTTTT,900,UUUUUUUUUUUUUU,690.00
M10001,VVVVVVV,900,JJJJJJJJJJJJJJ,1048.00
M10001,TTTTTTT,900,JJJJJJJJJJJJJJ,1048.00

The output you want to achieve is as follows:

M10001,VVVVVVV,900,UUUUUUUUUUUUUU,690.00
M10001,TTTTTTT,900,JJJJJJJJJJJJJJ,1048.00

If I was asked to interpret this output I would say that Technician VVVVVVV has worked on equipment M10001 and has used (or fitted) spare part UUUUUUUUUUUUUU to it, and that Technician TTTTTTT has worked on equipment M10001 and has used (or fitted) spare part JJJJJJJJJJJJJJ to it.

In other words there is an implied relationship between the Technician and the spare part in the output.  There may be a way to get the output you want in the way you want it but I would doubt the usefulness of the data output in this way.

If I was asked to provide this kind of information using a single query, the only way in which I can think of trying to provide a meaningful answer would be to provide the following information:

Equip Id Equip Name Total Labour Cost Total Parts Cost
-------- ---------- ----------------- ----------------
M10001   GENERATOR               1800          1738.00

If you need to find out which parts or which technician had worked on the equipment, this could be provided as 2 separate queries.

I think that you should re-analyse your requirement to display the data in this way and try to come up with a more meaningful way of showing the relationships between the 3 tables.

HTH

Mark