Professional Web Applications Themes

SQL help on joins - Dreamweaver AppDev

I'm getting bogged down in figuring out how to do joins on 3 tables. The three tables are Institution, stats and signoff. Institution_ID is the relationship between all tables. What I need to accomplish is I want to display records where an institution has entered in some data into the stats table but has not completed entering all data and therefore would not have an entry in the signoff table (Institution_ID IS NULL). I can do an outer join on two of the tables but as soon as I introduce the third, it just doesn't work. Can anyone suggest what ...

  1. #1

    Default SQL help on joins

    I'm getting bogged down in figuring out how to do joins on 3 tables. The three
    tables are Institution, stats and signoff. Institution_ID is the relationship
    between all tables. What I need to accomplish is I want to display records
    where an institution has entered in some data into the stats table but has not
    completed entering all data and therefore would not have an entry in the
    signoff table (Institution_ID IS NULL).

    I can do an outer join on two of the tables but as soon as I introduce the
    third, it just doesn't work. Can anyone suggest what I need to do to make this
    work?

    Thanks.

    zCrow Guest

  2. #2

    Default Re: SQL help on joins

    FROM Institution I INNER JOIN Stats S ON I.Institution_ID = S.Institution_ID
    LEFT OUTER JOIN Signoff O ON I.Institution_ID=O.Institution_ID
    WHERE O.Institution_ID IS NULL

    You'll have to use either SELECT DISTINCT or the GROUP BY clause to get each
    institution, else you'll have it listed once for each record in the stats
    table.

    Alternatively:
    FROM Institution I LEFT OUTER JOIN Signoff O ON
    I.Institution_ID=O.Institution_Id
    WHERE O.Institution_ID IS NULL AND I.Institution_ID IN (SELECT DISTINCT
    Institution_ID FROM Stats)

    In this case, a normal SELECT is fine.


    "zCrow" <webforumsusermacromedia.com> wrote in message
    news:d5oand$fij$1forums.macromedia.com...
    > I'm getting bogged down in figuring out how to do joins on 3 tables. The
    > three
    > tables are Institution, stats and signoff. Institution_ID is the
    > relationship
    > between all tables. What I need to accomplish is I want to display records
    > where an institution has entered in some data into the stats table but has
    > not
    > completed entering all data and therefore would not have an entry in the
    > signoff table (Institution_ID IS NULL).
    >
    > I can do an outer join on two of the tables but as soon as I introduce the
    > third, it just doesn't work. Can anyone suggest what I need to do to make
    > this
    > work?
    >
    > Thanks.
    >

    Lionstone Guest

  3. #3

    Default Re: SQL help on joins

    Lionstone - again you are there with sql help. You helped me out last week as well. I haven't tried it yet but will shortly.

    Thanks for taking the time for doing this.
    zCrow Guest

  4. #4

    Default Re: SQL help on joins

    Not a problem.

    "zCrow" <webforumsusermacromedia.com> wrote in message
    news:d5qje1$rsb$1forums.macromedia.com...
    > Lionstone - again you are there with sql help. You helped me out last week
    > as well. I haven't tried it yet but will shortly.
    >
    > Thanks for taking the time for doing this.

    Lionstone Guest

Similar Threads

  1. Alternative joins
    By Murdoc in forum MySQL
    Replies: 0
    Last Post: August 18th, 10:31 AM
  2. Joins or subqueries
    By alexford in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 26th, 02:31 AM
  3. JOINs instead of AND
    By Jim Michaels in forum MySQL
    Replies: 1
    Last Post: March 11th, 02:26 AM
  4. JOINs in Views
    By Martin Lingl in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 11:36 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139