CODEWITHSUNDEEP
mysqlsqlsql-server
Shayal Singh
Shayal Singh

Reputation: 69

How to Display row even if a record does not exist in another table?

I Have a table called books

books
 - bookid
 - userid(fk)
 - bookname

And I Have a table named paid

paid
 - paidid(pk)
 - userid(fk)
 - link
 - bookid(fk)

My query :

SELECT * 
FROM books,paid
WHERE books.bookid = paid.bookidfk
AND books.useridfk = '$userid'";

That only shows records that have been paid for but I still want to display all books from the user even if its not paid for.

Upvotes: 0

Views: 466

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46223

You can use a LEFT JOIN to return all books for the user along with paid data when available. ANSI 92 syntax:

SELECT  *
FROM    books
        LEFT JOIN paid ON books.bookid = paid.bookidfk
WHERE   books.useridfk = '$userid';

I suggest you avoid using the * shorthand in production code and instead specify the desired list of columns.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

It sounds like you're looking for an outer join:

select *
from books b
   left join paid p on b.bookid = p.bookidfk
where b.useridfk = '$userid';

Upvotes: 1

Related Questions