Can anyone help with SQL?

[size13]

It's best to show an example of what I want to do...

Say I have one table with a choice of options e.g.

 
ID  DESC 
1   TVR 
2   FORD 
3   RENAULT 
etc... 

And another table e.g.

 
NAME  CAR1  CAR2  CAR3 
RC     1     3     2 
JS     3     1     2 

How do I get it to show something like...

 
NAME   CAR1     CAR2     CAR3 
RC     TVR      RENAULT  FORD 
JS     RENAULT  TVR      FORD 

with one sql statement?

I'm not a student asking for answers to a project! And the real project has nothing to do with cars - so it's slightly on-topic.

The only other way I can think of would be to get the results back then do an individual lookup on the second table to find the car name out.

Ta

Richard

[plotloss]

You need to create a join between the two files using the common key of each file.

Matt.

[fatsteve]

Table 1 : CARS

  
ID DESC  
1  TVR  
2  FORD  
3  RENAULT  

Table 2 : OWNERS

 
NAME CAR1 CAR2 CAR3  
RC   1    3    2  
JS   3    1    2  

SQL :

  
SELECT o.name, c1.desc, c2.desc, c3.desc  
FROM owner o, car c1, car c2, car c3  
WHERE o.car1 = c1.id  
AND o.car2 = c2.id  
AND o.car3 = c3.id  

Probably best to use an outer join on the CAR tables in-case each owner does NOT have a value for all 3 cars, Hence :

  
SELECT o.name, c1.desc, c2.desc, c3.desc  
FROM owner o, car c1, car c2, car c3  
WHERE o.car1(+) = c1.id  
AND o.car2(+) = c2.id  
AND o.car3(+) = c3.id  

The outer join syntax is Oracle standard but you get the idea.

Yawn!!


Steve

>> Edited by fatsteve on Friday 4th April 11:43

[pdv6]

Or have a look at the [Extended Order Details] view in the Northwind database that comes with SQL Server, if you have it to hand...

[alunr]

If you have access build it in there then goto the SQL view to see how the joins are done

Highly reccomend SQL for Dummies also - I've been doing this for years and still go back to it!

[boiler]

You need to join the first table once for each of the columns you want to lookup in the second table. To achieve this you will need to use aliases for the second table. So you would end up with something like :
(I have named the first table car_makes and the second table owners).


select owners.name,
car1.desc,
car2.desc,
car3.desc
from owners,
car_makes car1,
car_makes car2,
car_makes car3
where owners.car1 = car1.id
and owners.car2 = car2.id
and owners.car3 = car3.id

You also have to be careful if some of the 'car' columns might be empty - ie some owners do not have 3 cars.

Edited to add fatsteve beat me to it.

Hope this helps,
Andy.

>> Edited by boiler on Friday 4th April 11:52

[fatsteve]

I'd agree totally with Aluns sentiments. The query builder in access is a good way to learn how the syntax works.

[pdv6]

fatsteve said: I'd agree totally with Aluns sentiments. The query builder in access is a good way to learn how the syntax works.

Thirded!
You just have to then learn which of the 10 zillion brackets that Access inserts can be ditched!

[size13]

[quot]
SELECT o.name, c1.desc, c2.desc, c3.desc FROM owner o, car c1, car c2, car c3 WHERE o.car1(+) = c1.id AND o.car2(+) = c2.id AND o.car3(+) = c3.id
The outer join syntax is Oracle standard but you get the idea.

Yawn!!
[/quot]

Will I not get an error from this as I'm tring to outer join one table to more that one other?

...

ORA-01417: a table may be outer joined to at most one other table
Cause: A table in a join operation specified an outer join to more than one other table. A table may specify an outer join to only one other table.

Action: Specify only one outer join (+) to this table, then retry the operation.

[size13]

I'm still stuck, here's most of the actual statement and the error...

   
Wrote file afiedt.buf   
   
  1  select a.illno, b1.servicetp, b2.servicetp, b3.servicetp, b4.servicetp, b5.servicetp   
  2  from v3ill.ill_details a, v3ill.ill_servicetps b1, v3ill.ill_servicetps b2,   
  3  v3ill.ill_servicetps b3, v3ill.ill_servicetps b4, v3ill.ill_servicetps b5   
  4  where   
  5  a.service_type1 (+) = b1.servicetp   
  6  and a.service_type2 (+) = b2.servicetp   
  7  and a.service_type3 (+) = b3.servicetp   
  8  and a.service_type4 (+) = b4.servicetp   
  9  and a.service_type5 (+) = b5.servicetp   
 10* and a.illno = 2407008   
SQL> /   
and a.service_type4 (+) = b4.servicetp   
                        *   
ERROR at line 8:   
ORA-01417: a table may be outer joined to at most one other table

edited to say it is Oracle 8.1.6

>> Edited by size13 on Friday 4th April 15:01

[fatsteve]

oh gawd, I always spend hours getting the(+) on the right side. Change:

 
5  a.service_type1 (+) = b1.servicetp      
6  and a.service_type2 (+) = b2.servicetp      
7  and a.service_type3 (+) = b3.servicetp      
8  and a.service_type4 (+) = b4.servicetp      
9  and a.service_type5 (+) = b5.servicetp    
 
to 
 
5  a.service_type1 = b1.servicetp(+)      
6  and a.service_type2 = b2.servicetp(+)      
7  and a.service_type3 = b3.servicetp(+)      
8  and a.service_type4 = b4.servicetp(+)      
9  and a.service_type5 = b5.servicetp(+)    

[size13]

Thanks Steve, I always thought the (+) went where you first put it cos that's the field that might be empty!

[fatsteve]

No worries, I did the first replay off my head so didn't try the syntax!!