Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 01 Mar 2008 18:50:56 +0100, Gilles Ganault
<[EMAIL PROTECTED]> wrote:
>This doesn't work as intended, because it returns all the rows,
>effectively ignoring the WHERE part:
Thanks everyone for the help. Problem solved:
$dbh = new PDO("sqlite:test.sqlite");
$sql = "SELECT Table1.*,Table2.name FROM Table1,Table2 WHERE
Table1.table2id=1 AND Table2.id=1";
$rows = $dbh->query($sql);
echo "";
while($row = $rows->fetch(PDO::FETCH_NUM) ) {
echo "\n";
for($i=0;$i$row[$i]\n";
}
echo "\n";
}
echo "";
$dbh = null;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 18:23:43 -0500, "Stephen Oberholtzer" <[EMAIL PROTECTED]> wrote: >I have to ask: Why is it that you expected a condition applying to one >column on one table, to also apply to a differently named column in a >differently named table? Because I'm not clear about how joins work :-) Thanks for the help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, Mar 1, 2008 at 6:13 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sat, 1 Mar 2008 18:04:12 -0500, "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote: > >> INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); > >> INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); > >> = > >> INSERT INTO Table1 VALUES (NULL,"John Doe",1); > >> INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); > >> = > >> SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; > >> = > >> 1|John Doe|1|1|Some text in Table2 > >> 1|John Doe|1|2|Some other text in Table2 > >> = > > > > >I'm confused. Which one of those rows does not have table1.table2id=1? > > Sorry for the imprecision: It's the same record, but why do I get two > rows instead of one? I expected only the first one, since "Some text > in Table2" has its ID = 1. Then you need to specify that: SELECT * FROM Table1,Table2 WHERE Table1.table2id=1 AND Table2.ID=1; I have to ask: Why is it that you expected a condition applying to one column on one table, to also apply to a differently named column in a differently named table? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 18:04:12 -0500, "Stephen Oberholtzer" <[EMAIL PROTECTED]> wrote: >> INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); >> INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); >> = >> INSERT INTO Table1 VALUES (NULL,"John Doe",1); >> INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); >> = >> SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; >> = >> 1|John Doe|1|1|Some text in Table2 >> 1|John Doe|1|2|Some other text in Table2 >> = > >I'm confused. Which one of those rows does not have table1.table2id=1? Sorry for the imprecision: It's the same record, but why do I get two rows instead of one? I expected only the first one, since "Some text in Table2" has its ID = 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 15:35:01 -0500, "P Kishor"
<[EMAIL PROTECTED]> wrote:
>SELECT table1.*, table2.col1
>FROM table1 JOIN table2 ON table1.field10 = table2.field1
Thanks for the tip, but...
1. I want to SELECT all the columns from Table1 and one column from
Table2, but only rows WHERE table1.field10 = 1. The above SELECT
returns all the rows from Table1.
I put the SQL commands in my other reply, so it makes more sense.
2. I can't use the "." notation because PHP doesn't allow this as
column names in associative arrays.
For instance, this doesn't work:
===
$dbh = new PDO("sqlite:test.sqlite");
$sql = "SELECT Table1.*, Table2.name FROM Table1 JOIN Table2 ON
Table1.table2id = Table2.id";
$rows = $dbh->query($sql);
while($row = $rows->fetch(PDO::FETCH_ASSOC) ) {
//Nothing shown...
echo sprintf("%s",$row['Table1.name']);
}
$dbh = null;
===
So the solution I found is use absolute names for all columns, eg.
Table1_table2id. I should have specified this in the orignal post.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
> = > CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, > table2id INTEGER); > CREATE TABLE Table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); > = > INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); > INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); > = > INSERT INTO Table1 VALUES (NULL,"John Doe",1); > INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); > = > SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; > = > 1|John Doe|1|1|Some text in Table2 > 1|John Doe|1|2|Some other text in Table2 > = > > I expected only the first row, but I got two :-/ I'm confused. Which one of those rows does not have table1.table2id=1? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 15:27:19 -0500, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >What do you mean, all the rows? Are you saying the resultset contains >some rows where table1_field10 is not equal to table2_field1? With all >due respect, I find it very hard to believe. That's what happens, though. FWIW, I'm using 3.5.6: = CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, table2id INTEGER); CREATE TABLE Table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); = INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); = INSERT INTO Table1 VALUES (NULL,"John Doe",1); INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); = SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; = 1|John Doe|1|1|Some text in Table2 1|John Doe|1|2|Some other text in Table2 = I expected only the first row, but I got two :-/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On 3/1/08, Gilles Ganault <[EMAIL PROTECTED]> wrote: > Hello > > I have two tables: Table1 has about 10 columns, Table2 has 2. I need > to get all the columns of Table1 and only one column in Table2 where > some field in Table1 is equal to field1 in Table2. > > This doesn't work as intended, because it returns all the rows, > effectively ignoring the WHERE part: > > SELECT * FROM table1,table2 WHERE table1_field10=table2_field1; > > => Is there a smarter way to solve the problem than replacing "*" with > every single column, ie. > > SELECT table1_field1, table1_field2... table1_field10,table2_field1, > table2_field2 FROM table1,table2 WHERE table1.field10=table2.field1; > SELECT table1.*, table2.col1 FROM table1 JOIN table2 ON table1.field10 = table2.field1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
"Gilles Ganault" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have two tables: Table1 has about 10 columns, Table2 has 2. I need > to get all the columns of Table1 and only one column in Table2 where > some field in Table1 is equal to field1 in Table2. > > This doesn't work as intended, because it returns all the rows, > effectively ignoring the WHERE part: > > SELECT * FROM table1,table2 WHERE table1_field10=table2_field1; What do you mean, all the rows? Are you saying the resultset contains some rows where table1_field10 is not equal to table2_field1? With all due respect, I find it very hard to believe. > => Is there a smarter way to solve the problem than replacing "*" with > every single column, ie. Realize that the list of columns in the SELECT clause has no effect on the number of rows in the resultset. It only limits how much of each row you can see. If you feel there are rows in the resultset that shouln't be there, changing the column list won't help you solve that. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Alternative to * to SELECT most columns?
Hello I have two tables: Table1 has about 10 columns, Table2 has 2. I need to get all the columns of Table1 and only one column in Table2 where some field in Table1 is equal to field1 in Table2. This doesn't work as intended, because it returns all the rows, effectively ignoring the WHERE part: SELECT * FROM table1,table2 WHERE table1_field10=table2_field1; => Is there a smarter way to solve the problem than replacing "*" with every single column, ie. SELECT table1_field1, table1_field2... table1_field10,table2_field1, table2_field2 FROM table1,table2 WHERE table1.field10=table2.field1; Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
