SELECT_Join problem... :)

Assassin · Oct 5, 1999

Lets say i hawe Tables A,B,C. 

A is the client table, B is the product table and C contains warrant replacments, it contains 2 id fileds from A and same for B, now i need to create select that joins it all togetger, how te heck i can do that? 

 

A) 

Name 

ID 

 

B) 

Name 

ID 

 

C) 

BadProduct (B.Name) 

Worker (A.Name) 

NewProduct (B.Name) 

ReplacedBy (A.Name) 

ID 

 

Select ????????????? FROM C ??????? JOIN ??????? WHERE C.ID=1 

 

 

************HELP!!!!! *********************

MikeLacey · Oct 5, 1999

This is ANSI SQL (only kind I know) so I hope that's ok. 
 
Select a.field, b.field, c.field 
FROM A,B,C 
WHERE C.ID=1 
and c.id=a.id 
and c.id=b.id; 
 
Regards 
 
Mike 
 
Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href= > </a>

carp · Oct 6, 1999

How about: 
 
SELECT replacement.id, 
old_product.name, 
old_worker.name, 
new_product.name, 
new_worker.name, 
FROM a old_worker, 
a new_worker, 
b old_product, 
b new_product, 
c replacement 
WHERE 
replacement.id = 1 
AND replacement.worker = old_worker.name 
AND replacement.bad_product = old_product.name 
AND replacement.replaced_by = new_worker.name 
AND replacement.newproduct = new_product.name; 
 
My only comment would be that it might for a better design to use the ID columns from tables A and B in Table C instead of the name columns.

calahans · Oct 7, 1999

I like Mike's best - its simple and should give you all the data you need 
 
C

carp · Oct 11, 1999

Yes, Mike's solution is much simpler and will work if A, B, and C all share the same ID value. 
 
However, I believe the problem is that table C is really defined as (Assassin - Please confirm): 
 
BadProduct (B.ID%TYPE) 
Worker (A.ID%TYPE) 
NewProduct (B.ID%TYPE) 
ReplacedBy (A.ID%TYPE) 
ID 
 
If Worker <> ReplacedBy and/or BadProduct <> NewProduct within any given row, then I believe you would have to use my query (or something like it) in order to extract the names of all clients and products involved. 
 
For instance, if Table A contains rows 
 
ID Name 
1 Smith 
2 Jones 
 
 
and Table B contains 
 
ID Name 
1 Metal Rotor 
2 Plastic Rotor 
3 Carbon Rotor 
 
 
and Table C contains 
 
ID BadProduct Worker NewProduct ReplacedBy 
1 1 1 2 2 
 
I believe Mike's query would look something like (Mike - is this what you meant or did I misread you?) : 
 
SQL> select a.name "1st Worker", 
b.name "1st Part", 
a.name "2nd Worker", 
b.name "2nd Part" 
from a,b,c 
where c.id = 1 
and c.id = a.id 
and c.id = b.id; 
 
1st Worker 1st Part 2nd Worker 2nd Part 
-------------------- -------------------- -------------------- -------------------- 
Smith Metal Rotor Smith Metal Rotor 
 
which would not be a correct response here. 
 
That is why my query includes multiple references to the same table. In this case, my query/response looks like: 
 
SELECT replacement.id, 
old_product.name "1st Part", 
old_worker.name "1st Worker", 
new_product.name "2nd Part", 
new_worker.name "2nd Worker" 
FROM a old_worker, 
a new_worker, 
b old_product, 
b new_product, 
c replacement 
WHERE 
replacement.id = 1 
AND replacement.worker = old_worker.id 
AND replacement.badproduct = old_product.id 
AND replacement.replacedby = new_worker.id 
AND replacement.newproduct = new_product.id 
 
ID 1st Part 1st Worker 2nd Part 2nd Worker 
--------- -------------------- -------------------- -------------------- -------------------- 
1 Metal Rotor Smith Plastic Rotor Jones 
 
 
Assassin - do either of these solve your problem? 
(Sorry about the formatting, but blank spaces keep getting eliminated and TAB doesn't work here!)

MikeLacey · Oct 17, 1999

You lost me there carp - sorry 
 
Actually - Assasin hasn't logged in since (s)he posted the question. <smile> Shame. 
 
Maybe our comments will assist someone else. 
 
-ml 
 
Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SELECT_Join problem... :)

Assassin

Programmer

MikeLacey

MIS

carp

MIS

calahans

Programmer

carp

MIS

MikeLacey

MIS

Similar threads

Part and Inventory Search

Sponsor