#1052 - Column 'status' in field list is ambiguous MySQL

Question

I have created 3 tables.

books (id, name, author, edition, status, quantitity, department, category)
issued_book (username, book_id, status, issue_date, return_date)
users (id, postition, fristname, lastname, username,email, contat, password)

I get this error when executed the following query.

#1052 - Column 'status' in field list is ambiguous

Query:

SELECT users.username
     ,position
     ,books.id
     ,name
     ,author
     ,edition
     ,status 
  FROM users 
  join issued_books 
    ON users.username = issued_books.username   
  join books 
    ON issued_books.book_id = books.id 
 WHERE issued_books.status =''

Can someone provide me with the executable query?

Use Alias with table names or table_name.column_name pattern for this type of problem — Akhilesh Mishra, Jan 13 '21 at 07:54
Also, given that id is the PRIMARY KEY of the users table, this is what you should use for your FK, not username — Strawberry, Jan 13 '21 at 08:06

Akhilesh Mishra · Answer 1 · 2021-01-13T08:05:25.420

0

You should use alias like below:

SELECT 
u.username,
u.position, 
b.id,
b.name,
b.author, 
b.edition,
b.status 
FROM users u 
inner join issued_books i ON u.username=i.username 
inner join books b ON i.book_id=b.id WHERE i.status =''

edited Jan 13 '21 at 08:05

answered Jan 13 '21 at 07:57

Akhilesh Mishra

5,876
3
16
32

score 0 · Answer 2 · edited Jan 13 '21 at 10:43

0

SELECT u.username, u.position, b.id, b.name, b.author, b.edition, b.status 
FROM users u 
INNER JOIN issued_books ib ON u.username=ib.username 
INNER JOIN books b ON ib.book_id=b.id WHERE ib.status =''

edited Jan 13 '21 at 10:43

Sabito stands with Ukraine

4,271
8
34
56

answered Jan 13 '21 at 08:04

Red Black

13
3

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Donald Duck Jan 13 '21 at 12:31

#1052 - Column 'status' in field list is ambiguous MySQL

2 Answers2