Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT 
    CONCAT_WS(' ',child_fname,child_sname) AS 'Child Name'
    , child_gender AS 'Gender'
    , child_dob AS 'DoB'
    , COUNT activity_name AS 'Activities name'
    , CONCAT_WS(' ', parent_title, parent_fname, parent_sname) AS 'Parent Name'
    , parent_phone AS 'Parent phone'
    , CONCAT_WS(' ', parent_address1, parent_address2, parent_town,
        parent_county parent_pcode) AS 'Parent Address',
FROM 
    Child,
    JOIN ChildActivity USING(child_id),
    JOIN Activities USING(activity_id),
    JOIN Parent USING(parent_id),
WHERE 
    activity_name='Art';

Query browser says I have error near 'AS Activities name... CONCAT_WS('',...at line 4.

How do I resolve this error?

share|improve this question
2  
Are you sure you are using SQL-Server and not MySQL? –  ypercube Jul 22 '12 at 7:35
add comment

1 Answer 1

There are several problems in your query.

  • First, there is no CONCAT_WS() function. I assume you want to concatenate the strings. Use the + operator instead if you are using SQL-Server:

    child_fname + ' ' + child_sname AS "Child Name",
    

    or if you are using MySQL, the || operator or CONCAT() function:

    child_fname || ' ' || child_sname AS "Child Name",
    

    or (Correction, update), just keep the CONCAT_WS() function, it's suitable in your case, if you are using MySQL.

  • Second, there are several commas in wrong places (before the FROM and and the JOIN keywords.

  • Third, the aliases should be quoted either with double quotes (like: "Child Name") or brackets (like: [Child Name]) or not at all if they have no special characters and are not reserved (like: Gender). Don't use single quotes for column or table aliases. Single quotes are for string literals, like 'Art'. MySQL may allow you to use them in aliases, too, but don't fall into this bad habit.
  • Fourth, the COUNT() is an (aggregate) function and it needs parenthesis. But since you have no GROUP BY, there is no point in using COUNT().
  • And last, SQL-Server does not like the USING syntax for joins, so use the ON instead (which works in all DBMS).

So, the query becomes:

SELECT 
    CONCAT_WS(' ', child_fname, child_sname) AS "Child Name",
    child_gender AS Gender,
    child_dob AS DoB,
    activity_name AS "Activities name",
    CONCAT_WS(' ', parent_title, parent_fname, parent_sname) AS "Parent Name",
    parent_phone AS "Parent phone",
    CONCAT_WS(' ', parent_address1, parent_address2, parent_town, parent_county, parent_pcode)
      AS "Parent Address"                 --<-- Comma removed here.

--- for MySQL
FROM Child                                --<-- Also here
  JOIN ChildActivity USING(child_id)      --<-- and here
  JOIN Activities USING(activity_id)      --<-- and here
  JOIN Parent USING(parent_id)            --<-- and here.

--- for SQL-Server or MySQL
--- FROM Child 
---   JOIN ChildActivity ON ChildActivity.child_id = Child.child_id  
---   JOIN Activities ON Activities.activity_id = ChildActivity.activity_id  
---   JOIN Parent ON Parent.parent_id = Child.parent_id     

WHERE activity_name = 'Art' ;
share|improve this answer
1  
SQL Server doesn't support USING either. Though I suspect they have mistagged as when I execute the original query in SQL Server it complains 'CONCAT_WS' is not a recognized built-in function name. rather than the message given in the OP. –  Martin Smith Jul 22 '12 at 7:35
    
@Martin: Yes, it's probably in MySQL and the error is from the COUNT line. –  ypercube Jul 22 '12 at 7:36
    
@MartinSmith Thank you so much I really appreciate your answers, they worked perfectly. –  Stella Jul 24 '12 at 8:26
    
@ypercube Thank you so much i appreciate you answers, you really made my day. –  Stella Jul 24 '12 at 8:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.