Home » RDBMS Server » Server Administration » Using Alias's with INNER JOIN
Using Alias's with INNER JOIN [message #372325] Fri, 02 February 2001 11:57 Go to next message
dana
Messages: 7
Registered: February 2001
Junior Member
Hello,
I have a syntax problem with the inner join statement. I have two tables one called mix and one called product. Each record in the mix table has 8 ingrediants from the product tale (FK as long in Mix = PK in product) and 8 amounts for the volume of each product in the mix. I want to resolve other data about the products such as its name given a mix so I inner joined the two as shown here.

SELECT MIX_TABLE.*, PRODUCT_TABLE.Product_Name
FROM PRODUCT_TABLE INNER JOIN MIX_TABLE ON (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Resin_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Solvent1_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Solvent2_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Solvent3_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Solvent4_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Solvent5_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Solvent6_Product_DBID) AND (PRODUCT_TABLE.Product_DBID=MIX_TABLE.Catalyst_Product_DBID ) ;

What I would like is to be able to show the catalyst name which is a product the resin name and so on and so forth. I have tried to use AS to alias but do not seem to have the syntax down. At the front of the select I would like to say

SELECT MIX_TABLE.*, Catalyst.Product_Name,Resin.Product_Name, Solvent1.Product_Name
FROM PRODUCT_TABLE INNER JOIN MIX_TABLE ON........

Any hel in this area would be appreciated.

Thanks
Dana Stephenson
elvicash@yahoo.com

Report message to a moderator

Re: Using Alias's with INNER JOIN [message #372328 is a reply to message #372325] Fri, 02 February 2001 13:38 Go to previous messageGo to next message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
In oracle, there are 2 types of joins:
1.) Inner join (syntax a.id=b.id)
2.) Outer join (syntax a.id=b.id(+) or a.id(+)=b.id)

Does this example help?

create table profile (id number, description varchar2(20));

insert into profile values(1, 'First Profile');

insert into profile values(2, 'Second Profile');

insert into profile values(3, 'Third Profile');

create table t1 (id number, type varchar2(10), value varchar2(20));

insert into t1 values(1, 'Name', 'Oracle');

insert into t1 values(1, 'Age', '30');

insert into t1 values(1, 'sex', 'Male');

insert into t1 values(2, 'Name', 'Orafans');

insert into t1 values(2, 'Age', '25');

insert into t1 values(2, 'sex',null);

insert into t1 values(3, 'Name', 'bwithu');

insert into t1 values(3, 'Age', null);

insert into t1 values(3, 'sex', null);

select p.id, p.description, a.value ||' '||b.value||' '||c.value All_together
from profile p, t1 a, t1 b, t1 c
where p.id= a.id
and p.id = b.id
and p.id = c.id
and a.type = 'Name'
and b.type = 'Age'
and c.type = 'sex';

Report message to a moderator

Re: Using Alias's with INNER JOIN [message #372383 is a reply to message #372328] Tue, 06 February 2001 17:36 Go to previous message
Dana S.
Messages: 1
Registered: February 2001
Junior Member
Hi Andrew,
I got it to work myself here is what I did after more study on the problem. I was having problems with the AS after the inner joins but I did get it to work and then I can just use the alias names in the select portion. Thanks for the help. Dana
SELECT MIX_TABLE.*, RESIN_TABLE.Product_Name, SOLVENT1_TABLE.Product_Name, SOLVENT2_TABLE.Product_Name, SOLVENT3_TABLE.Product_Name, SOLVENT4_TABLE.Product_Name, SOLVENT5_TABLE.Product_Name, CATALYST_TABLE.Product_Name
FROM (((((((MIX_TABLE INNER JOIN PRODUCT_TABLE AS RESIN_TABLE ON MIX_TABLE.Resin_Product_DBID=RESIN_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS SOLVENT1_TABLE ON MIX_TABLE.Solvent1_Product_DBID=SOLVENT1_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS SOLVENT2_TABLE ON MIX_TABLE.Solvent2_Product_DBID=SOLVENT2_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS SOLVENT3_TABLE ON MIX_TABLE.Solvent3_Product_DBID=SOLVENT3_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS SOLVENT4_TABLE ON MIX_TABLE.Solvent4_Product_DBID=SOLVENT4_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS SOLVENT5_TABLE ON MIX_TABLE.Solvent5_Product_DBID=SOLVENT5_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS SOLVENT6_TABLE ON MIX_TABLE.Solvent6_Product_DBID=SOLVENT6_TABLE.Product_DBID) INNER JOIN PRODUCT_TABLE AS CATALYST_TABLE ON MIX_TABLE.Catalyst_Product_DBID=CATALYST_TABLE.Product_DBID;

Report message to a moderator

Previous Topic: sql
Next Topic: How do I run an sql program in Oracle?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 15 21:05:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.