INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Syntax

Using a Table Twice in One Query (SELF JOINS) by danceman
Posted: 13 Jul 02

I post this as an aid to a question I see posted in this forum and others.  The samples posted here are simple and for the purpose of giving guidance to joining a table to itself.  I have used this method with a database that included and entire parts and assembly of an aircraft.  It was used to provided not only assembly but how one part affected other parts.

***** Table setup for EMP *****
CREATE TABLE 'CAR' FREE (EMPNAME C(20), ;
                        EMPID N(3,0), ;
                        EMPREPORT2 N(3,0))

***** Create each index for EMP *****
SET COLLATE TO 'MACHINE'
INDEX ON EMPID TAG EMPID CANDIDATE

INSERT INTO emp (empname,empid) VALUES ('john doe',100)
INSERT INTO emp VALUES ('wayne smith'   ,101,100)
INSERT INTO emp VALUES ('doris jones'   ,102,100)
INSERT INTO emp VALUES ('martha comarow',103,102)
INSERT INTO emp VALUES ('jane doe'      ,104,101)
INSERT INTO emp VALUES ('mark gill'     ,105,101)
INSERT INTO emp VALUES ('jake brawn'    ,106,102)
INSERT INTO emp VALUES ('jill band'     ,107,101)
INSERT INTO emp VALUES ('robert diggs'  ,108,102)

WAIT WINDOW "display all employees and report to"
select emp.empname as employee, sup.empname as manager ;
    from emp ;
           join emp as sup on emp.empreport2 = sup.empid
WAIT WINDOW "Display all employees and report to with Top Level"
select emp.empname as employee, sup.empname as manager ;
    from emp ;
        left join emp as sup on emp.empreport2 = sup.empid
WAIT WINDOW "Select manager and suborinates"
select emp.empname as employee, sup.empname as manager ;
       from emp INNER join emp as sup ;
            on emp.empreport2 = sup.empid ;
       WHERE EMP.EMPID = 102 OR EMP.EMPREPORT2 = 102


SECOND EXAMPLE

***** Table setup for EMP *****
CREATE TABLE 'CAR' FREE (PARTNAME C(20), ;
                           PARTID N(3,0), ;
                           MEMBEROF N(3,0))

INSERT INTO car (partname,partid) VALUES ('auto',100)
INSERT INTO car VALUES ('engine'      ,101,100)
INSERT INTO car VALUES ('body'        ,102,100)
INSERT INTO car VALUES ('carbarator'  ,103,101)
INSERT INTO car VALUES ('air filter'  ,104,101)
INSERT INTO car VALUES ('fuel pump'   ,105,101)
INSERT INTO car VALUES ('front window',106,102)
INSERT INTO car VALUES ('front seat'  ,107,102)
INSERT INTO car VALUES ('carpet'      ,108,102)

WAIT WINDOW "display all parts and assembly"
select car.partname as carpart, assem.partname as assembly ;
    from car join car as assem on car.memberof = assem.partid

WAIT WINDOW "Display all parts and assemblys with top assembly"
select car.partname as carpart, assem.partname as assembly ;
    from car left join car as assem on car.memberof = assem.partid

WAIT WINDOW "Select assembly and parts"
select car.partname as carpart, assem.partname as assembly ;
    from car left join car as assem on car.memberof = assem.partid;
    WHERE car.partID = 102 OR car.memberof = 102

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close