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

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
mysqlsqldatabasephpmyadmindbeaver


Solution 1:[1]

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%'

Solution 2:[2]

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

SELECT * FROM table
JOIN table2 ON 
WHERE
GROUP BY

Solution 3:[3]

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

Solution 4:[4]

Try to follow everything in order which goes like:

SELECT - FROM - JOIN & ON - WHERE - GROUP BY - HAVING - ORDER BY

Solution 5:[5]

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%" ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hari
Solution 2 Karl Hill
Solution 3 Sunny
Solution 4 Kuro Neko
Solution 5 Kuro Neko

Related Questions