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

Question

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

1

`WHERE` goes after `JOIN`. – Barmar Feb 08 '22 at 05:39

score 0 · Answer 1 · answered Feb 10 '22 at 13:31

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

score 0 · Answer 2 · edited Feb 11 '22 at 17:43

0

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

SELECT * FROM table
JOIN table2 ON 
WHERE
GROUP BY

edited Feb 11 '22 at 17:43

Karl Hill

12,937
5
58
95

answered Feb 11 '22 at 15:17

lebron Brian

5
1
1

score 0 · Answer 3 · edited Apr 23 '22 at 15:18

0

Try to follow everything in order which goes like:

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

edited Apr 23 '22 at 15:18

Kuro Neko

795
12
19

answered Feb 11 '22 at 16:12

Dogukan

1
1

score 0 · Answer 4 · answered Feb 11 '22 at 19:19

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

score 0 · Answer 5 · edited Apr 23 '22 at 23:24

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

edited Apr 23 '22 at 23:24

Kuro Neko

795
12
19

answered Apr 22 '22 at 20:22

Saransh Lakra

52
6

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 23 '22 at 01:14

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

5 Answers5