Professional Web Applications Themes

Another SQL syntax problem - MySQL

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 . ...

  1. #1

    Default Another SQL syntax problem

    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 Guest

  2. #2

    Default Re: Another SQL syntax problem


    Matthew256 wrote: 
    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 Guest

  3. #3

    Default Re: Another SQL syntax problem


    Captain Paralytic wrote: 
    > 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)[/ref]

    That did it.
    Thanks for your help!

    Matthew

    Matthew256 Guest

  4. #4

    Default Re: Another SQL syntax problem


    Matthew256 wrote: 
    > > 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)[/ref]
    >
    > That did it.
    > Thanks for your help!
    >
    > Matthew[/ref]

    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 Guest

  5. #5

    Default Re: Another SQL syntax problem


    Matthew256 wrote: 
    > >
    > > That did it.
    > > Thanks for your help!
    > >
    > > Matthew[/ref]
    >
    > 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[/ref]

    The really strange part is, if I take off the first line it works fine. 

    Matthew256 Guest

Similar Threads

  1. SQL syntax problem
    By reversed.K@gmail.com in forum MySQL
    Replies: 2
    Last Post: November 15th, 09:33 PM
  2. syntax problem
    By Mikael Hellström in forum ASP Database
    Replies: 12
    Last Post: April 7th, 09:40 PM
  3. INSERT MAX(ID) Syntax problem
    By Targa in forum ASP Database
    Replies: 2
    Last Post: May 1st, 08:01 PM
  4. IE problem with -> syntax
    By Patrick in forum PHP Development
    Replies: 1
    Last Post: July 31st, 03:55 PM
  5. Syntax error problem
    By Stephany Young in forum Informix
    Replies: 1
    Last Post: July 17th, 04:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139