--00504502b1ab5c44b80474e59291
Content-Type: text/plain; charset=ISO-8859-1
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=products.Item (left join categories on
products.Category=categories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
--00504502b1ab5c44b80474e59291--
Joins aren't nested like that, unless you use a subquery. I think you just=
need to remove the parens around the second join.
For better help:
1. show the real SQL -- echo the statement. Most people here don't like lo=
oking at app code because your variables could contain anything.
2. Give the exact error message
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
5. Explain what you're trying to accomplish.
Regards,
Gavin Towey
-----Original Message-----
From: Victor Subervi [mailto:victorsubervi@gmail.com]
Sent: Thursday, October 01, 2009 1:34 PM
To: mysql@lists.mysql.com
Subject: Nested Joins
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=3Dproducts.Item (left join categories on
products.Category=3Dcategories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
--00504502b619dbb99d0474e6480b
Content-Type: text/plain; charset=ISO-8859-1
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
> Joins aren't nested like that, unless you use a subquery. I think you just
> need to remove the parens around the second join.
>
I tried that and no go :(
>
> For better help:
> 1. show the real SQL -- echo the statement. Most people here don't like
> looking at app code because your variables could contain anything.
>
> ProgrammingError: (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1"), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
>
> Isn't that the same thing?
> 3. If there's no error, explain what you expect and what you're getting
>
> 4. Include table schema
>
> DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
> 5. Explain what you're trying to accomplish.
>
> cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The "client" in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
--00504502b619dbb99d0474e6480b--
--_000_30B3DF511CEC5C4DAE4D0D29050475341956DD1312AAApmgiloca l_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that str=
ing, which we can't see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=3Dp.Item jo=
in categories cat on p.Category=3Dcat.ID
Make your code produce the above, and you should be fine. I suspect you do=
n't need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=3DIntroduction_to_Joins
For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-d=
b-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsubervi@gmail.com]
Sent: Thursday, October 01, 2009 2:25 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
Joins aren't nested like that, unless you use a subquery. I think you just=
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like lo=
oking at app code because your variables could contain anything.
ProgrammingError: (1064, "You have an error in your SQL syntax; check the m=
anual that corresponds to your MySQL server version for the right syntax to=
use near ') left join categories on products.Category=3Dcategories.ID)' at=
line 1"), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on %s.Item=3Dproducts=
..Item left join categories on products.Category=3Dcategories.ID;' % (client=
, client))
The "client" in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in produ=
cts, where all the information about the products is to be found, EXCEPT th=
e name of the category. For that, we have to go to the categories table.
Hope that makes it clear.
TIA,
V
________________________________
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--_000_30B3DF511CEC5C4DAE4D0D29050475341956DD1312AAApmgiloca l_--
--001485354cc28eafab0474e6d243
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
Well, your syntax is *exactly* what I had (with a few cosmetic changes).
I've been over the MySQL manual on joins with no luck. I'll read over your
resources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey
> Victor,
>
>
>
> Thank you for the information, that was helpful.
>
>
>
> At least part of the problem is the variables you are replacing in that
> string, which we can=92t see.
>
>
>
> The statement should be something like:
>
>
>
> select * from ben_franklin_planners c join products p on c.Item=3Dp.Item =
join
> categories cat on p.Category=3Dcat.ID
>
>
>
> Make your code produce the above, and you should be fine. I suspect you
> don=92t need LEFT JOIN there, an inner join will suffice.
>
>
>
> For more info on joins:
>
> http://hashmysql.org/index.php?title=3DIntroduction_to_Joins
>
> For more indepth info:
> http://dev.mysql.com/tech-resources/articles/mysql-db-design -ch5.pdf
>
> http://dev.mysql.com/doc/refman/5.1/en/join.html
>
>
>
> Regards,
>
> Gavin Towey
>
>
>
> *From:* Victor Subervi [mailto:victorsubervi@gmail.com]
> *Sent:* Thursday, October 01, 2009 2:25 PM
> *To:* Gavin Towey; mysql@lists.mysql.com
> *Subject:* Re: Nested Joins
>
>
>
> On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
>
> Joins aren't nested like that, unless you use a subquery. I think you ju=
st
> need to remove the parens around the second join.
>
>
> I tried that and no go :(
>
>
> For better help:
> 1. show the real SQL -- echo the statement. Most people here don't like
> looking at app code because your variables could contain anything.
>
> ProgrammingError: (1064, "You have an error in your SQL syntax; check th=
e
> manual that corresponds to your MySQL server version for the right syntax=
to
> use near ') left join categories on products.Category=3Dcategories.ID)' a=
t
> line 1"), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
>
>
> 2. Give the exact error message
>
> Isn't that the same thing?
>
>
> 3. If there's no error, explain what you expect and what you're getting
>
> 4. Include table schema
>
> DESCRIBE `ben_franklin_planners`
> ID int(4) unsigned NULL
> Item int(4) unsigned NULL
> Discount int(2) unsigned NULL
>
> DESCRIBE categories
> ID int(3) primary key not NULL auto_increment
> Category varchar(20) unique NULL
>
> describe products
> ID int(4) primary key not NULL
> Category int(3) NULL
> Item varchar(20) UNIQUE NULL
> Description varchar(255) NULL
> UOM varchar(20) NULL
> Price float(7,2) NULL
>
>
>
> 5. Explain what you're trying to accomplish.
>
> cursor.execute('select * from %s left join products on
> %s.Item=3Dproducts.Item left join categories on
> products.Category=3Dcategories.ID;' % (client, client))
>
> The "client" in this case is ben_franklin_planners
> ben_franklin_planners has an item # that is the same as the item # in
> products, where all the information about the products is to be found,
> EXCEPT the name of the category. For that, we have to go to the categorie=
s
> table.
>
> Hope that makes it clear.
> TIA,
> V
>
>
>
> ------------------------------
> The information contained in this transmission may contain privileged and
> confidential information. It is intended only for the use of the person(s=
)
> named above. If you are not the intended recipient, you are hereby notifi=
ed
> that any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended recipie=
nt,
> please contact the sender by reply email and destroy all copies of the
> original message.
>
--001485354cc28eafab0474e6d243--
--_000_30B3DF511CEC5C4DAE4D0D29050475341956DD1320AAApmgiloca l_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Victor,
Just noticed, the join condition from client to productions should be chang=
ed:
select * from ben_franklin_planners c join products p on c.Item=3Dp.ID join=
categories cat on p.Category=3Dcat.ID
If you're still getting syntax errors you need to check your variables. Tr=
y assigning the query you're building to a string, then printing it out so =
you know *exactly* what you're sending to mysql.
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsubervi@gmail.com]
Sent: Thursday, October 01, 2009 3:04 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes). I'=
ve been over the MySQL manual on joins with no luck. I'll read over your re=
sources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that str=
ing, which we can't see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=3Dp.Item jo=
in categories cat on p.Category=3Dcat.ID
Make your code produce the above, and you should be fine. I suspect you do=
n't need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=3DIntroduction_to_Joins
For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-d=
b-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsubervi@gmail.com
Sent: Thursday, October 01, 2009 2:25 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
Joins aren't nested like that, unless you use a subquery. I think you just=
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like lo=
oking at app code because your variables could contain anything.
ProgrammingError: (1064, "You have an error in your SQL syntax; check the m=
anual that corresponds to your MySQL server version for the right syntax to=
use near ') left join categories on products.Category=3Dcategories.ID)' at=
line 1"), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on %s.Item=3Dproducts=
..Item left join categories on products.Category=3Dcategories.ID;' % (client=
, client))
The "client" in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in produ=
cts, where all the information about the products is to be found, EXCEPT th=
e name of the category. For that, we have to go to the categories table.
Hope that makes it clear.
TIA,
V
________________________________
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
________________________________
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--_000_30B3DF511CEC5C4DAE4D0D29050475341956DD1320AAApmgiloca l_--
--0015175770eac9e1340474f34c27
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
Thanks! Got it! I was missing the word "from"...
select * FROM %s c ...
Thanks again ;)
V
On Thu, Oct 1, 2009 at 5:08 PM, Gavin Towey
> Victor,
>
>
>
> Just noticed, the join condition from client to productions should be
> changed:
>
>
>
> select * from ben_franklin_planners c join products p on c.Item=3Dp.ID jo=
in
> categories cat on p.Category=3Dcat.ID
>
>
>
> If you=92re still getting syntax errors you need to check your variables.
> Try assigning the query you=92re building to a string, then printing it o=
ut so
> you know **exactly** what you=92re sending to mysql.
>
>
>
> Regards,
>
> Gavin Towey
>
>
>
> *From:* Victor Subervi [mailto:victorsubervi@gmail.com]
> *Sent:* Thursday, October 01, 2009 3:04 PM
>
> *To:* Gavin Towey; mysql@lists.mysql.com
> *Subject:* Re: Nested Joins
>
>
>
> Well, your syntax is *exactly* what I had (with a few cosmetic changes).
> I've been over the MySQL manual on joins with no luck. I'll read over you=
r
> resources tonight. Any other ideas would be appreciated.
> Thanks,
> V
>
> On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey
>
> Victor,
>
>
>
> Thank you for the information, that was helpful.
>
>
>
> At least part of the problem is the variables you are replacing in that
> string, which we can=92t see.
>
>
>
> The statement should be something like:
>
>
>
> select * from ben_franklin_planners c join products p on c.Item=3Dp.Item =
join
> categories cat on p.Category=3Dcat.ID
>
>
>
> Make your code produce the above, and you should be fine. I suspect you
> don=92t need LEFT JOIN there, an inner join will suffice.
>
>
>
> For more info on joins:
>
> http://hashmysql.org/index.php?title=3DIntroduction_to_Joins
>
> For more indepth info:
> http://dev.mysql.com/tech-resources/articles/mysql-db-design -ch5.pdf
>
> http://dev.mysql.com/doc/refman/5.1/en/join.html
>
>
>
> Regards,
>
> Gavin Towey
>
>
>
> *From:* Victor Subervi [mailto:victorsubervi@gmail.com]
> *Sent:* Thursday, October 01, 2009 2:25 PM
> *To:* Gavin Towey; mysql@lists.mysql.com
> *Subject:* Re: Nested Joins
>
>
>
> On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
>
> Joins aren't nested like that, unless you use a subquery. I think you ju=
st
> need to remove the parens around the second join.
>
>
> I tried that and no go :(
>
>
> For better help:
> 1. show the real SQL -- echo the statement. Most people here don't like
> looking at app code because your variables could contain anything.
>
> ProgrammingError: (1064, "You have an error in your SQL syntax; check th=
e
> manual that corresponds to your MySQL server version for the right syntax=
to
> use near ') left join categories on products.Category=3Dcategories.ID)' a=
t
> line 1"), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
>
>
> 2. Give the exact error message
>
> Isn't that the same thing?
>
>
> 3. If there's no error, explain what you expect and what you're getting
>
> 4. Include table schema
>
> DESCRIBE `ben_franklin_planners`
> ID int(4) unsigned NULL
> Item int(4) unsigned NULL
> Discount int(2) unsigned NULL
>
> DESCRIBE categories
> ID int(3) primary key not NULL auto_increment
> Category varchar(20) unique NULL
>
> describe products
> ID int(4) primary key not NULL
> Category int(3) NULL
> Item varchar(20) UNIQUE NULL
> Description varchar(255) NULL
> UOM varchar(20) NULL
> Price float(7,2) NULL
>
>
>
> 5. Explain what you're trying to accomplish.
>
> cursor.execute('select * from %s left join products on
> %s.Item=3Dproducts.Item left join categories on
> products.Category=3Dcategories.ID;' % (client, client))
>
> The "client" in this case is ben_franklin_planners
> ben_franklin_planners has an item # that is the same as the item # in
> products, where all the information about the products is to be found,
> EXCEPT the name of the category. For that, we have to go to the categorie=
s
> table.
>
> Hope that makes it clear.
> TIA,
> V
>
>
>
>
> ------------------------------
>
> The information contained in this transmission may contain privileged and
> confidential information. It is intended only for the use of the person(s=
)
> named above. If you are not the intended recipient, you are hereby notifi=
ed
> that any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended recipie=
nt,
> please contact the sender by reply email and destroy all copies of the
> original message.
>
>
>
> ------------------------------
> The information contained in this transmission may contain privileged and
> confidential information. It is intended only for the use of the person(s=
)
> named above. If you are not the intended recipient, you are hereby notifi=
ed
> that any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended recipie=
nt,
> please contact the sender by reply email and destroy all copies of the
> original message.
>
--0015175770eac9e1340474f34c27--