Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Rules for Joins on Multiple Tables in the FROM

    Thanks for reading. Easily solvable, but I'm curious about the logic behind it.
    This is for the following tables (Galleon, an open-source ColdFusion forum):
    Code:
    CREATE TABLE galleon_groups (
    id NVARCHAR(35) NOT NULL PRIMARY KEY,
    group NVARCHAR(50) NOT NULL
    );
    
    CREATE TABLE galleon_rights (
    id NVARCHAR(35) NOT NULL PRIMARY KEY,
    right NVARCHAR(255) NOT NULL,
    );
    
    CREATE TABLE galleon_conferences (
    id NVARCHAR(35) NOT NULL PRIMARY KEY,
    name NVARCHAR(255) NOT NULL
    );
    I want to populate a table with each row from the `rights` table and each row from the `groups` table (nothing to join on) and join the row from the `conferences` table that corresponds to the `groups` table. When I run the following, it works:
    Code:
    SELECT g.id,c.id,r.id
    FROM dbo.galleon_rights r,dbo.galleon_groups g
    INNER JOIN galleon_conferences c ON c.name=g.[group]
    WHERE g.[group] NOT IN ('Test1','forumsmember') AND r.[right]!='CanView';
    However, if I do this (switching the order of the tables in the FROM), it fails, throwing a "The multi-part identifier g.[group] could not be bound." error.
    Code:
    SELECT g.id,c.id,r.id
    FROM dbo.galleon_groups g,dbo.galleon_rights r
    INNER JOIN galleon_conferences c ON c.name=g.[group]
    WHERE g.[group] NOT IN ('Test1','forumsmember') AND r.[right]!='CanView';
    So, basically, if you join on a table within a query with multiple tables in the FROM clause, it can't be joined on the first table in that list of the FROM clause?
    Anyone have any insight on this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coatimundi View Post
    I want to populate a table with each row from the `rights` table and each row from the `groups` table (nothing to join on)
    this sounds like you want a UNION

    what columns do you want to select from those two tables to insert into the target table? and what does the target table look like?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    5
    Thanks for the response.

    A union would separate the recordsets. I need them together, within the same recordset, as in a join. Joining, but not joining on anything. Each of the records from the `rights` table need to have their own record for each record in the `groups` table. So:
    Code:
    group right
    ---------------
    1       1
    1       2
    1       3
    2       1
    2       2
    ...etc.

    The fields I need for the query are in the SELECT list of the query I posted.

  4. #4
    Join Date
    Aug 2011
    Posts
    5
    I should elaborate and clarify that I'm not really looking for a fix for this. Aside from switching the order of the tables in the FROM, I could do something like "LEFT JOIN galleon_rights r ON 1=1" and it would work as I'd like it to.
    I'm more curious as to why the first query I posted works, while the second fails, with the only difference the order of the tables in the FROM clause.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coatimundi View Post
    A union would separate the recordsets. I need them together, within the same recordset, as in a join. Joining, but not joining on anything. Each of the records from the `rights` table need to have their own record for each record in the `groups` table.
    you'rew not going to get that with SQL

    i'm not sure i know what to advise you to do (how's your copy/paste skill with excel?)

    but what you're looking for here is not gonna happen with sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've implicitly used a CROSS JOIN on r. Have you tried explicitly using a CROSS JOIN?
    Code:
    CREATE TABLE a (
       aid		INT			NOT NULL
    )
    
    CREATE TABLE b (
       bid		Char(1)			NOT NULL
    )
    
    INSERT INTO a (aid)
       SELECT 0 UNION SELECT 2 UNION SELECT 3
    
    INSERT INTO b (bid)
       SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'
    
    
    SELECT *
       FROM a
       CROSS JOIN b
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aaargh, i just read pat's reply and then re-read post #3

    please forget my earlier comment, a CROSS JOIN is exactly what you need

    and i need a vacation...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937 View Post
    and i need a vacation...
    Me too! Where shall we go?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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