Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Unanswered: Several Inner Joins(SQL)

    I read that Access needs "nested" joins for this to work? Can someone show me for the following SQL statement?:

    SELECT pc.tagnum, pc.location
    FROM employee
    INNER JOIN PC ON (pc.empnum=employee.empnum)
    INNER JOIN SOFTWARE ON (software.tagnum=pc.tagnum)
    WHERE employee.empnum=567
    AND software.packid NOT IN (SELECT DISTINCT packid FROM PACKAGE WHERE packtype=’Database’)

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smalvarez View Post
    I read that Access needs "nested" joins for this to work? Can someone show me for the following SQL statement?:
    sure, it's easy...
    Code:
    SELECT pc.tagnum
         , pc.location 
      FROM (
           employee
    INNER 
      JOIN pc 
        ON pc.empnum = employee.empnum
           )
    INNER 
      JOIN software 
        ON software.tagnum = pc.tagnum
     WHERE employee.empnum = 567
       AND software.packid NOT IN 
           ( SELECT DISTINCT packid 
               FROM package 
              WHERE packtype = 'Database' )
    note carefully which parts of the FROM clause are contained within the parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    5
    thanks I also did this:

    SELECT DISTINCT pc.tagnum, pc.location
    FROM employee,pc,software,package
    WHERE (pc.empnum=employee.empnum)
    AND (software.tagnum=pc.tagnum)
    AND
    employee.empnum=567
    AND software.packid NOT IN (SELECT packid FROM PACKAGE WHERE packtype='Database')

  4. #4
    Join Date
    Sep 2010
    Posts
    5
    Where do I put the paratheneses with more then 2 inner joins?

    ie:

    SELECT employee.EMPNAME, employee.EMPNUM
    FROM employee
    INNER JOIN PC ON (pc.empnum=employee.empnum)
    INNER JOIN SOFTWARE ON (software.tagnum=pc.tagnum)
    INNER JOIN PACKAGE ON (software.packid=package.packid)
    WHERE package.PACKTYPE=’Database’

    THANKS!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT employee.empname
         , employee.empnum
      FROM (
           (
           employee
    INNER 
      JOIN pc 
        ON pc.empnum = employee.empnum
           )
    INNER 
      JOIN software 
        ON software.tagnum = pc.tagnum
           )
    INNER 
      JOIN package 
        ON package.packid = software.packid
     WHERE package.PACKTYPE = 'Database'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •