CODEWITHSUNDEEP
phpmysql
jonboy
jonboy

Reputation: 2749

Select Multiple Rows From Same Table

I have two tables 'book' and 'category'. How can I display all the rows from my 'book' table? Note, there is a join between category(book table) and cat_name(category table).

book table

id   category    title
1    2           a title here
2    2           a title here
3    1           a title here
4    Comedy      a title here
5    1           a title here
6    Horror      a title here

category table

id       cat_name   
----     -------         
1        Language
2        Kids

Currently I am using the following sql query, it only seems to display rows that have a number for the category, whereas I want all the data. Currently my html table doesn't display rows 4 and 6.

SELECT b.id, b.title, b.category, b.author, b.isbn, b.publicationYear,
        c.cat_name
        FROM book AS b
        INNER JOIN category AS c ON b.category = c.id

Sample of my php

echo '<td>' . $row['id'] . '</td>';
echo '<td>' . $row['title'] . '</td>';
echo '<td>' . $row['cat_name'] . '</td>';   
echo '<td>' . $row['author'] . '</td>';
echo '<td>' . $row['isbn'] . '</td>';

Quite new to php/mysql so any advice or direction is appreciated.

Upvotes: 0

Views: 273

Answers (2)

Youssef
Youssef

Reputation: 54

try with this i think it solved your problem

SELECT b.id, 
       b.title, 
       b.category, 
       b.author, 
       b.isbn, 
       b.publicationYear,
       c.cat_name
FROM book AS b , category AS c
WHERE b.category = c.id

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You are doing inner join and this will return only matching item. If you want all item from left table use left join instead

    SELECT b.id, 
           b.title, 
           b.category, 
           b.author, 
           b.isbn, 
           b.publicationYear,
           c.cat_name
    FROM book AS b
    left JOIN 
    category AS c 
    ON
    b.category = c.id

UPDATE :

Doing left join will show null for the cat_name where it does not match with the category table

Work around would be as

SELECT 
b.id, 
b.title, 
b.category, 
b.author, 
b.isbn, 
b.publicationYear,
case 
 when cast(b.category as unsigned)= 0 then b.category
 else c.cat_name 
end as cat_name 
FROM book AS b
left JOIN category AS c ON b.category = c.id

Upvotes: 2

Related Questions