#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Atlanta, GA
    Posts
    3
    Rep Power
    0
    I'm new to MySQL, but fairly adept with MS SQL Server. I've written the following select statement that fails with a syntax error when executed in MySQL. I have referred to the MySQL manual and toyed with the syntax a bit, but with no success. I am confident its not the CONCAT, as I have used the same CONCAT syntax elsewhere with success. Any guidance would be appreciated...

    SELECT
    CASE
    WHEN sq.presuf = 0 THEN concat(pl.name, ' ', sq.tag)
    ELSE concat( sq.tag,' ', pl.name)
    END as name,
    pl.email,
    pl.icq
    from squads sq, players pl
    where sq.name = pl.squadname
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Atlanta, GA
    Posts
    3
    Rep Power
    0
    whoops...thats
    SELECT
    CASE
    WHEN sq.presuf = 0 THEN concat(pl.name, ' ', sq.tag)
    ELSE concat( sq.tag,' ', pl.name)
    END as name,
    pl.email,
    pl.icq
    from squads as sq, players as pl
    where sq.name = pl.squadname
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    It's not the concat as MySQL supports that, but MySQL does not support CASE.

    You need to use an IF() construct:

    select if(sq.presuf=0,concat(pl.name,' ',sq.tag),concat(sq.tag,' ',pl.name)) as name,
    pl.email,
    pl.icq
    from squads sq, players pl
    where sq.name=pl.squadname;

    Note: you do NOT use AS when aliasing the table name... you had it correct in your first post.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Atlanta, GA
    Posts
    3
    Rep Power
    0
    Thanks Rod..That worked perfectly.

    According to the MySQL manual, case statements are supported, and the manual
    also states that tables are aliased using 'as'. Pretty obvious case doesn't work the way it does in MS T-SQL tho...anyway, it worked, and thanks for your help!
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Oops, you're right about using AS to alias tables, just never done it that way or seen it done that way.

    You are also correct about CASE, however, are you using the correct manual for the version of MySQL you are running. CASE wasn't added until 3.23.3

Similar Threads

  1. Mysql problem.
    By Nik in forum MySQL Help
    Replies: 54
    Last Post: December 21st, 2006, 07:02 PM
  2. DB2 SQL Optimazation
    By act7656 in forum DB2 Development
    Replies: 1
    Last Post: June 8th, 2004, 11:49 PM
  3. Replies: 2
    Last Post: January 30th, 2004, 10:11 AM
  4. Combine 3 SELECT statements - MySQL 3.23
    By amorgan in forum MySQL Help
    Replies: 1
    Last Post: December 1st, 2003, 06:21 PM
  5. assembly representation of switch statements
    By infamous41md in forum C Programming
    Replies: 3
    Last Post: November 27th, 2003, 06:03 AM

IMN logo majestic logo threadwatch logo seochat tools logo