OK, another newbie with a dumb question. Why doesn't my query work?

SELECT pmainT.product + '.75' + woodtype.short AS SKU, psubT.ID + .75
AS id, ROUND(psubT.price * 1.65, 2) AS price,
psubT.pmain_ID + .75 AS Expr1, psubT.woodtype_ID, psubT.weight * .75 AS
weight, psubT.oldweight, psubT.turning
FROM woodtype INNER JOIN
pmainT INNER JOIN
psubT ON pmainT.ID = psubT.pmain_ID ON woodtype.ID = psubT.woodtype_ID
WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)

MySQL said:
#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 'ON woodtype . ID = psubT . woodtype_ID WHERE ( pmainT . milling
= 1 ) AND ( pma' at line 1

Matthew256 wrote:
> OK, another newbie with a dumb question. Why doesn't my query work?
>
> SELECT pmainT.product + '.75' + woodtype.short AS SKU, psubT.ID + .75
> AS id, ROUND(psubT.price * 1.65, 2) AS price,
> psubT.pmain_ID + .75 AS Expr1, psubT.woodtype_ID, psubT.weight * .75 AS
> weight, psubT.oldweight, psubT.turning
> FROM woodtype INNER JOIN
> pmainT INNER JOIN
> psubT ON pmainT.ID = psubT.pmain_ID ON woodtype.ID = psubT.woodtype_ID
> WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)
>
> MySQL said:
> #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 'ON woodtype . ID = psubT . woodtype_ID WHERE ( pmainT . milling
> = 1 ) AND ( pma' at line 1
Because you have your JOINs and ONs all mixed up.
Try this (tidied up a bit):

SELECT
pmainT.product + '.75' + woodtype.short AS SKU,
psubT.ID + .75 AS id,
ROUND(psubT.price * 1.65, 2) AS price,
psubT.pmain_ID + .75 AS Expr1,
psubT.woodtype_ID,
psubT.weight * .75 AS weight,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT ON pmainT.ID = psubT.pmain_ID
WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)

Captain Paralytic wrote:
> Matthew256 wrote:
> > OK, another newbie with a dumb question. Why doesn't my query work?
> >
> > SELECT pmainT.product + '.75' + woodtype.short AS SKU, psubT.ID + .75
> > AS id, ROUND(psubT.price * 1.65, 2) AS price,
> > psubT.pmain_ID + .75 AS Expr1, psubT.woodtype_ID, psubT.weight * .75 AS
> > weight, psubT.oldweight, psubT.turning
> > FROM woodtype INNER JOIN
> > pmainT INNER JOIN
> > psubT ON pmainT.ID = psubT.pmain_ID ON woodtype.ID = psubT.woodtype_ID
> > WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)
> >
> > MySQL said:
> > #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 'ON woodtype . ID = psubT . woodtype_ID WHERE ( pmainT . milling
> > = 1 ) AND ( pma' at line 1
> Because you have your JOINs and ONs all mixed up.
> Try this (tidied up a bit):
>
> SELECT
> pmainT.product + '.75' + woodtype.short AS SKU,
> psubT.ID + .75 AS id,
> ROUND(psubT.price * 1.65, 2) AS price,
> psubT.pmain_ID + .75 AS Expr1,
> psubT.woodtype_ID,
> psubT.weight * .75 AS weight,
> psubT.oldweight,
> psubT.turning
> FROM woodtype
> INNER JOIN psubT ON woodtype.ID = psubT.woodtype_ID
> INNER JOIN pmainT ON pmainT.ID = psubT.pmain_ID
> WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)

That did it.
Thanks for your help!

Matthew

Matthew256 wrote:
> Captain Paralytic wrote:
> > Matthew256 wrote:
> > > OK, another newbie with a dumb question. Why doesn't my query work?
> > >
> > > SELECT pmainT.product + '.75' + woodtype.short AS SKU, psubT.ID + .75
> > > AS id, ROUND(psubT.price * 1.65, 2) AS price,
> > > psubT.pmain_ID + .75 AS Expr1, psubT.woodtype_ID, psubT.weight * .75 AS
> > > weight, psubT.oldweight, psubT.turning
> > > FROM woodtype INNER JOIN
> > > pmainT INNER JOIN
> > > psubT ON pmainT.ID = psubT.pmain_ID ON woodtype.ID = psubT.woodtype_ID
> > > WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)
> > >
> > > MySQL said:
> > > #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 'ON woodtype . ID = psubT . woodtype_ID WHERE ( pmainT . milling
> > > = 1 ) AND ( pma' at line 1
> > Because you have your JOINs and ONs all mixed up.
> > Try this (tidied up a bit):
> >
> > SELECT
> > pmainT.product + '.75' + woodtype.short AS SKU,
> > psubT.ID + .75 AS id,
> > ROUND(psubT.price * 1.65, 2) AS price,
> > psubT.pmain_ID + .75 AS Expr1,
> > psubT.woodtype_ID,
> > psubT.weight * .75 AS weight,
> > psubT.oldweight,
> > psubT.turning
> > FROM woodtype
> > INNER JOIN psubT ON woodtype.ID = psubT.woodtype_ID
> > INNER JOIN pmainT ON pmainT.ID = psubT.pmain_ID
> > WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)
>
> That did it.
> Thanks for your help!
>
> Matthew

Here's another query question.
I know this looks crazy, but it's a simplified version of my query. I
need to do about five UNION ALLs in the subquery.

CREATE VIEW pmain AS
SELECT original,
ID,
type_ID,
dimensions,
leg_name,
product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize
FROM
(SELECT 1 AS original,
ID,
type_ID,
dimensions,
leg_name,
product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize
FROM pmainT
WHERE (active = 1)
) AS a
WHERE (active = 1)

MySQL said:
#1349 - View's SELECT contains a subquery in the FROM clause

Matthew256 wrote:
> Matthew256 wrote:
> > Captain Paralytic wrote:
> > > Matthew256 wrote:
> > > > OK, another newbie with a dumb question. Why doesn't my query work?
> > > >
> > > > SELECT pmainT.product + '.75' + woodtype.short AS SKU, psubT.ID + .75
> > > > AS id, ROUND(psubT.price * 1.65, 2) AS price,
> > > > psubT.pmain_ID + .75 AS Expr1, psubT.woodtype_ID, psubT.weight * .75 AS
> > > > weight, psubT.oldweight, psubT.turning
> > > > FROM woodtype INNER JOIN
> > > > pmainT INNER JOIN
> > > > psubT ON pmainT.ID = psubT.pmain_ID ON woodtype.ID = psubT.woodtype_ID
> > > > WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)
> > > >
> > > > MySQL said:
> > > > #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 'ON woodtype . ID = psubT . woodtype_ID WHERE ( pmainT . milling
> > > > = 1 ) AND ( pma' at line 1
> > > Because you have your JOINs and ONs all mixed up.
> > > Try this (tidied up a bit):
> > >
> > > SELECT
> > > pmainT.product + '.75' + woodtype.short AS SKU,
> > > psubT.ID + .75 AS id,
> > > ROUND(psubT.price * 1.65, 2) AS price,
> > > psubT.pmain_ID + .75 AS Expr1,
> > > psubT.woodtype_ID,
> > > psubT.weight * .75 AS weight,
> > > psubT.oldweight,
> > > psubT.turning
> > > FROM woodtype
> > > INNER JOIN psubT ON woodtype.ID = psubT.woodtype_ID
> > > INNER JOIN pmainT ON pmainT.ID = psubT.pmain_ID
> > > WHERE (pmainT.milling = 1) AND (pmainT.twosquares = 0)
> >
> > That did it.
> > Thanks for your help!
> >
> > Matthew
>
> Here's another query question.
> I know this looks crazy, but it's a simplified version of my query. I
> need to do about five UNION ALLs in the subquery.
>
> CREATE VIEW pmain AS
> SELECT original,
> ID,
> type_ID,
> dimensions,
> leg_name,
> product,
> leg_description,
> milling,
> oversize,
> trackproduction,
> active,
> height,
> SORT,
> customize
> FROM
> (SELECT 1 AS original,
> ID,
> type_ID,
> dimensions,
> leg_name,
> product,
> leg_description,
> milling,
> oversize,
> trackproduction,
> active,
> height,
> SORT,
> customize
> FROM pmainT
> WHERE (active = 1)
> ) AS a
> WHERE (active = 1)
>
> MySQL said:
> #1349 - View's SELECT contains a subquery in the FROM clause

The really strange part is, if I take off the first line it works fine.
> CREATE VIEW pmain AS