Paul Hastings wrote:
I think that is a limitation of MS SQL Server, I can't find any such
thing in the SQL standard and it works fine for me in PostgreSQL:
a plain jane inner join with identitical columns removed (manually) from the
select list is a natural join for sql server.
SELECT *
]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 4:30 PM
Subject: Re: SQL Multiple Reference Tables Question - Thanks
This is the answer. Many thanks.
James Brown
- Original Message -
From: Cantrell, Adam [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday
James, Dina is correct - 'LEFT JOIN' is the same as 'LEFT OUTER JOIN' for most
RDBMS's. Some will actually require LEFT OUTER JOIN, but I think that's rare these
days. The LEFT/LEFT OUTER is more loose - it's like telling the DB:
give me all the records in the left table, and then if there are
This is probably basic, since I am new at this, but I want to know what is
best. I have simplified the tables for illustration.
I have one main table
tblTHING
ThingKey NameColorKeyCatKey
MakerKey
-- --- --
James Brown wrote:
CFQUERY name=thinglist datasource=mydata
SELECT
tblThing.ThingKey,
tblThing.ThingName,
tblColor.ColorDescription,
tblCategory.CatDescription,
tblMaker.MakerName
FROM
tblThing,
tblColor,
tblCategory,
tblMaker
WHERE
You're probably wanting outer joins -- (syntax is different for Oracle)
select blah blah blah
from tblThing
left join tblMaker on ( tblMaker.makerid = tblThing.makerid )
left join tblColor on ( tblColor.colorid = tblThing.thingid )
This will return all the items in the tblThing table with 0 or
= #url.MakerKey#
/CFQUERY
Adam.
-Original Message-
From: James Brown [mailto:[EMAIL PROTECTED]
Sent: Friday, February 28, 2003 3:28 PM
To: CF-Talk
Subject: SQL Multiple Reference Tables Question
This is probably basic, since I am new at this, but I want to
know what is
best
Doesn't work, get the message:
the correlation name NATURAL is specified multiple times in a FROM clause
What I don't understand is - if the tables are structured in SQL Server as
being related by those fields, why SQL server doesn't just know what the
relationships are without my having to
James Brown wrote:
Doesn't work, get the message:
the correlation name NATURAL is specified multiple times in a FROM clause
I think that is a limitation of MS SQL Server, I can't find any such
thing in the SQL standard and it works fine for me in PostgreSQL:
jochemd= create table test1 (id1
This is the answer. Many thanks.
James Brown
- Original Message -
From: Cantrell, Adam [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 4:41 PM
Subject: RE: SQL Multiple Reference Tables Question
This may work, you'll want to look into using
SQL by 100%.
Adam.
-Original Message-
From: James Brown [mailto:[EMAIL PROTECTED]
Sent: Friday, February 28, 2003 4:15 PM
To: CF-Talk
Subject: Re: SQL Multiple Reference Tables Question
Doesn't work, get the message:
the correlation name NATURAL is specified multiple times
Much of my prior
experience was with xBase and once tables were related to
each other, all
one had to do was specify the field name in the child
table and the correct
related record was selected.
I think that gets very ugly with self-referencing tables
and recursive queries.
I can vouch
I think that is a limitation of MS SQL Server, I can't find any such
thing in the SQL standard and it works fine for me in PostgreSQL:
a plain jane inner join with identitical columns removed (manually) from the
select list is a natural join for sql server.
SELECT * becomes SELECT table1.a,
13 matches
Mail list logo
