Rules for Joins on Multiple Tables in the FROM

coatimundi · #1 (**permalink**) 08-19-11, 11:08

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?

r937 · #2 (**permalink**) 08-19-11, 12:05

Quote:

Originally Posted by coatimundi

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?

coatimundi · #3 (**permalink**) 08-19-11, 12:39

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.

coatimundi · #4 (**permalink**) 08-19-11, 12:50

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.

r937 · #5 (**permalink**) 08-19-11, 15:44

Quote:

Originally Posted by coatimundi

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

Pat Phelan · #6 (**permalink**) 08-19-11, 15:55

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

r937 · #7 (**permalink**) 08-19-11, 16:17

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...

Pat Phelan · #8 (**permalink**) 08-19-11, 16:23

Quote:

Originally Posted by r937

and i need a vacation...

Me too! Where shall we go?

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech