in reply to Triple joins in SQL::Statement

Why do you hide every single line of code of you experiments from us? Do you expect us to repeat every bit of your work in order to extend beyond where you got? By sharing your work so far, you make it easier for somebody to take the next step while also making your question much more interesting.

You might also point to the specific parts of SQL::Statement::Syntax that supported your conclusion. For example:

from clause ::= 
  table1 [, table2, ... tableN]
| table1 NATURAL [join_type] JOIN table2
| table1 [join_type] table2 USING (col1,col2, ... colN)
| table1 [join_type] JOIN table2 ON table1.colA = table2.colB

[download]

The ", table2, ... tableN" seems to quite clearly contradict only supporting joining between two tables. The other formats don't seem to support more than a pair of tables, though.

- tye        

Replies are listed 'Best First'.
Re^2: Triple joins in SQL::Statement (info)
by SilasTheMonk (Chaplain) on Oct 15, 2009 at 10:31 UTC

    Yes if I had taken the time to work through a small showable example I might have come to a different conclusion. The following code reproduces the problem. 

    #!/usr/bin/perl  -w
use DBI;
use Carp;
mkdir 'db';
my $dbh = DBI->connect('dbi:DBM:f_dir=db', undef, undef) || croak DBI-
+>errstr;
my @sql = (
       "create table cgiapp_pages (pageid varchar(255), lang varchar(2
+), internalid int)",
       "create table cgiapp_structure (internalid int)",
       "create table cgiapp_lang (lang varchar(2))",
       "insert into  cgiapp_pages (pageid, lang, internalid) values('t
+est1', 'en', 0)",
       "insert into  cgiapp_lang (lang) values('en')",
       "insert into  cgiapp_structure(internalid) values(0)",
        "SELECT * FROM cgiapp_pages p, cgiapp_lang l, cgiapp_structure
+ s WHERE p.lang = l.lang AND p.pageid = 'test1' AND p.internalid = s.
+internalid",
        );
foreach  my $c (@sql) {
        my $sth = $dbh->prepare($c) || croak $dbh->errstr;
        $sth->execute ||  croak $dbh->errstr;
        if ($c =~ /^SELECT/) {
                my $hash_ref = $sth->fetchrow_hashref;
                print $hash_ref ? %$hash_ref : "NO ROW";
        }
        $sth->finish;
}
$dbh->disconnect;
system('rm -rf db');

    [download]
    That is it prints "NO ROW" when you would not expect it to. The reason I now think I have an alternative explanation is that DBD:DBM does not by default support more than two columns (see href:http://search.cpan.org/~timb/DBI-1.609/lib/DBD/DBM.pm#Adding_multi-column_support_with_MLDBM). I have tried adapting the above code to use MLDBM but it seems to make no difference and also the "f_dir" attribute seems to be ignored.

    In fact I find that if I use DBD::CSV instead of DBM my simple example above works correctly. This rather suggests that SQL::Statement is not the problem. Still I have tried putting DBD::CSV back into my real-world problem and it hits other issues that I will not investigate right now.

[reply]
[d/l]

In Section Seekers of Perl Wisdom