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 INT, string1 text);
jochemd=> create table test2 (id2 INT, string2 text);
jochemd=> create table test3 (id3 INT, string3 text);
jochemd=> insert into test1 values (1, 'string 1');
jochemd=> insert into test2 values (2, 'string 2');
jochemd=> insert into test3 values (3, 'string 3');
jochemd=> create table test (id INT, id1 INT, id2 INT, id3 INT);
jochemd=> insert into test values (0,1,2,3);
jochemd=> select * from test natural join test1 natural join test2
natural join test3;
id3 | id2 | id1 | id | string1 | string2 | string3
-----+-----+-----+----+----------+----------+----------
3 | 2 | 1 | 0 | string 1 | string 2 | string 3
(1 row)
jochemd=>
Maybe you should file an enhancement request.
> 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 specify it.
The SQL standard requires you to specify the full relations.
> 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.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
