Subscribe: Comments on: Back to basics: inner joins
http://awads.net/wp/2006/03/20/back-to-basics-inner-joins/feed/
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
dept  deptno  eddie  emp passport  emp  equi  fariza  inner join  inner  join  joins  level  outer join  passport  select emp 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Comments on: Back to basics: inner joins

Comments on: ★ Back to basics: inner joins



News, views, tips and tricks on Oracle and other fun stuff



Last Build Date: Tue, 19 Aug 2014 05:55:04 +0000

 



By: Eddie Awad

Fri, 25 Apr 2008 22:15:52 +0000

FARIZA, The following joins will return the level in rows. Then you can write a stragg function to transform the level to a comma delimeted list of values.

By the way, level is reserved, can't use it in SQL unless you quote it.

 SELECT emp.name, emp.passport, edu."level"
   FROM emp, edu
  WHERE emp.passport = edu.passport;

Or using ANSI SQL:

 SELECT emp.name, emp.passport, edu."level"
   FROM emp inner join edu on emp.passport = edu.passport;



By: FARIZA

Thu, 24 Apr 2008 05:55:07 +0000

eDDIE , what i mean is like the senario bellow

emp ( name, passport PK) with 3 DATA : ('FORD','900000' ), ('CHANG','989999'),('CRISS;,'877777') table edu('passport' ,'level','result') with 6 DATA : ('900000' ,'DIPLOMA','3.4') ('900000' ,'DEGREE','3.0') ('900000' ,'MASTER','4.0') ('989999' ,'HIGHSCHOOL','2.0') ('989999' ,'DEGREE','3.0') ('877777' ,'HIGHSCHOOL','2.5')

RESULT SHOULD LIKE THIS

NAME PASSPORT LEVEL

FORD 900000 DIPLOMA DEGREE ASTER CHANG 989999 HIGHSCHOOL DEGREE CRISS 877777 HIGHSCHOOL

is it posibble to do the SQL join ? thanks so much




By: Eddie Awad

Thu, 24 Apr 2008 04:26:46 +0000

FARIZA, you join the two tables on the column a1. Then select columns from both tables.




By: FARIZA

Wed, 23 Apr 2008 23:12:15 +0000

Thanks Eddie for SQL MINUS Solution.

Another Question I have 2 table : T1 have a1,b1,c1 fields T2 have x2,x3,x4, a1 as FK from T1. These two table has a one to many relationship. How I want to display the two table as one record? exp result : a1,b1,c1,x2,x3,x4 a1,b1,c1,x3,x4 a1,b1,x2,x3,x4

thanks




By: Eddie Awad

Thu, 21 Feb 2008 16:54:19 +0000

FARIZA, your question is too general. You may want to take a look at the SQL MINUS operator as well as (NOT) EXISTS and (NOT) IN.




By: FARIZA

Thu, 21 Feb 2008 06:47:13 +0000

Eddie, how to list all infromation in table A that ni in table B, tq




By: Nigel

Sun, 14 Oct 2007 09:46:29 +0000

sreenivasulu

an INNER join is one that isn't an OUTER join. Or to be more specific: for a row to occur in the result relation, its component pieces must be present in BOTH of the source relations. An OUTER join, in contrast, can supply null values for "missing" rows (in either or both of the source relations, depending on whether this is a LEFT, RIGHT or FULL outer join).

an EQUI join is a special case join in which the predicates are based on equality conditions eg WHERE EMP.DEPTNO = DEPT.DEPTNO. Foreign key joins are examples of equijoins.

EQUI joins can be INNER or OUTER joins: SELECT EMP.NAME, ... , DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO -- inner join, equi join

or WHERE EMP.DEPTNO = DEPT.DEPTNO (+) -- outer join, equi join

In spite of being an old Oracle hacker, I actually prefer to use ANSI syntax as it is (I think) much more readable/maintainable, eg:

SELECT EMP.NAME, ... , DEPT.DNAME FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO -- outer join, equi join

HTH

Nigel




By: sreenivasulu

Thu, 11 Oct 2007 06:50:50 +0000

What is the difference between Equi and Inner Joins?




By: sreenivasulu

Thu, 11 Oct 2007 06:46:23 +0000

What is the difference between Inner(Simple) and Equi Joins? Could some explian me please?




By: Eddie Awad

Wed, 21 Mar 2007 16:34:49 +0000

Fariza, for example, if you have three tables, you would write a query like this:

SELECT emp.emp_name, dept.dept_name FROM emp INNER JOIN dept ON emp.dept_id = dept.dept_id INNER JOIN addresses ON emp.address_id = addresses.address_id