Select Multiple Rows From Same Table

Question

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.

Dudi Konfino · Accepted Answer · 2014-12-08 11:26:02Z

2

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

edited Dec 8, 2014 at 11:26

Dudi Konfino

1,1242 gold badges13 silver badges24 bronze badges

answered Dec 8, 2014 at 10:10

Abhik Chakraborty

44.9k6 gold badges55 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hartmut Holzgraefe Over a year ago

Also in the long run "Comedy" and "Horror" should be moved over to the category table and only be referenced by category id. Mixing category ids and verbatim names in book.category is bad design ...

Abhik Chakraborty Over a year ago

Yes since for non matching item cat_name becomes null, I have updated the answer with work-around.

jonboy Over a year ago

@Hartmut I realise "Comedy" and "Horror" should be moved over to the category table however these are added via Giigle Books Api and I haven't figured out how to do this yet!

Dudi Konfino · Accepted Answer · 2014-12-08 11:26:14Z

0

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

edited Dec 8, 2014 at 11:26

Dudi Konfino

1,1242 gold badges13 silver badges24 bronze badges

answered Dec 8, 2014 at 10:15

Youssef

545 bronze badges

Collectives™ on Stack Overflow

Select Multiple Rows From Same Table

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related