Question 63838 in PhpMyAdmin Question posted in
The official documentation can be found here.

Trying to get only cells with certain initials but giving me syntax error? SQL – Phpmyadmin

So my assignment question is to "Get a list of all books withdrawn by people with the initials ‘B.W.’. Show a column for the first name, last name, initials, and the title of the book". I am trying to join these 3 tables as it is the only way to get this information, but Im having a hard time only displaying names with initials B.W. I get a syntax error saying:

"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 ‘JOIN withdrawals

When I remove my ‘WHERE’ statement (all of the second line), my syntax error goes away. How do I make this work?

Below is my code:

 SELECT first_name, last_name, title FROM members 
     WHERE first_name LIKE 'B%' AND last_name LIKE 'W%'
      JOIN withdrawals  
        ON members.member_id  = withdrawals.member_id 
         JOIN books 
             ON withdrawals.book_id = books.book_id
Tags:

5

Answers


  1. 0

    JOIN should always be placed just after FROM table-name. The below should work

    SELECT first_name, last_name, title FROM members
    JOIN withdrawals
    ON members.member_id = withdrawals.member_id
    JOIN books
    ON withdrawals.book_id = books.book_id
    WHERE first_name LIKE ‘B%’ AND last_name LIKE ‘W%’

    Login or Signup to reply.
  2. 0

    There is a flow you have to follow when writing your SQL statements

    SELECT * FROM table
JOIN table2 ON 
WHERE
GROUP BY
    Login or Signup to reply.
  3. 0

    Try to follow everything in order which goes like:

    SELECT - FROM - JOIN & ON - WHERE - GROUP BY - HAVING - ORDER BY
    Login or Signup to reply.
  4. 0

    First, you will need to create a column called Initials. In this case, you can use SUBSTR to get the first letter from you first_name and the first letter from last_name. Then, you will need to CONCAT them together to make it a column.

    CONCAT(SUBSTR(first_name,1,1), '.', SUBSTR(last_name,1,1), '.') AS Initials

    After that, you need to find out who have the initials with B.W. . You can use LIKE (‘B.W.’). Since you have a specific answer, you do not need to use %.

    HAVING Initials LIKE ('B.W.')
    Login or Signup to reply.
  5. 0

    You can try this:

    SELECT CONCAT(SUBSTR(m.first_name,1,1) , '.', SUBSTR(m.last_name,1,1) ) AS "Initials"
FROM - JOIN - ON
WHERE m.first_name LIKE "B%" AND m.last_name LIKE "W%" ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search