where is my error in this sql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jaylin
Forum Commoner
Posts: 68
Joined: Fri Nov 18, 2005 4:44 am

where is my error in this sql query

Post by jaylin »

Code: Select all

$sql = "SELECT o.name as custname, od.* " .
	"FROM tblorder as o, orderdetail od, weekly as w " .
	"WHERE o.orderid = od.orderid and od.productid = w.id" .
	"ORDER BY o.orderid DESC";
regards,
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Re: where is my error in this sql query

Post by neophyte »

jaylin wrote:

Code: Select all

$sql = "SELECT o.name as custname, od.* " .
	"FROM tblorder as o, orderdetail od, weekly as w " .
	"WHERE o.orderid = od.orderid and od.productid = w.id" .
	"ORDER BY o.orderid DESC";
regards,
No need to concatenate at line breaks. "." doesn't concatenate. ".." would be good form.

Code: Select all

"SELECT o.name as custname, od.* FROM tblorder as o, orderdetail od, weekly as w WHERE o.orderid = od.orderid and od.productid = w.id ORDER BY o.orderid DESC";
Some one correct me if I'm wrong, queries from mutliple tables should be "JOIN" somehow. 'w' is not in your select statement.

Hope that gets you started.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

I think it's the way he's joining it, if the OP can show us the structure of the tables and exactly what he's trying to accomplish we can probably work something out, this should be in 'Databases' forum BTW
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: where is my error in this sql query

Post by Chris Corbyn »

neophyte wrote: "." doesn't concatenate. ".." would be good form.
It's a single dot to concatenate.

Code: Select all

echo "foo"."bar"; //Correct
echo "foo".."bar"; //Wrong
Yeah you're right, you don't need it just to use line breaks ;)
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Code: Select all

$sql = "SELECT o.name as custname, od.* " . 
   "FROM tblorder as o, orderdetail od, weekly as w " . 
   "WHERE o.orderid = od.orderid and od.productid = w.id" . 
   "ORDER BY o.orderid DESC";
you should try and use JOINs depending on what you are trying to achieve, as doing it this way if there are no details for the orderdetail or weekly tables to match the WHERE clauses it will leave that whole record out.

but that aside in your sql you need to change orderdetail od in your FROM to be orderdetail as od
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Like jshpro2 says. Theres no syntax error so perhaps if you explain what the problem is :)

Note: Am i right in thinking you're trying to join using two foreign keys and no primary?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Wayne wrote:but that aside in your sql you need to change orderdetail od in your FROM to be orderdetail as od
Not true. That's just shorthand SQL for aliasing ;)

Code: Select all

select foo from tbl as bob
Is the same as

Code: Select all

select foo from tbl bob
Moved to databases

EDITTED QUERIES, DONE THEM IN THE SELECT NOT FROM :P FIXED
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

Re: where is my error in this sql query

Post by sheila »

jaylin wrote:

Code: Select all

$sql = "SELECT o.name as custname, od.* " .
	"FROM tblorder as o, orderdetail od, weekly as w " .
	"WHERE o.orderid = od.orderid and od.productid = w.id" .
	"ORDER BY o.orderid DESC";
The only error I see is no space at the end of the 3rd line, after w.id
FWIW I would format it this way,

Code: Select all

$sql = "SELECT o.name as custname, od.* "
     . " FROM tblorder as o, orderdetail od, weekly as w "
     . " WHERE o.orderid = od.orderid and od.productid = w.id"
     . " ORDER BY o.orderid DESC";
Line up the . and always add a space after the first double quote.
Post Reply