[RESOLVED] How to display records from main table even if they don't exist in the join table?

[CodingLearner]

Hello, I'm new to php and mysql, I searched all over before I post this question here, so a little help is really appreciated.
I have 5 tables, which are:

books (which is the main table to join with others)
authors
covers
languages
locations

And I have 4 join tables to join between the table books with the other 4 tables:

books_authors
books_covers
books_languages
books_locations

The problem is, that when there is a book that has a record in the books table, and does not have in books_locations, or in any of the other join tables, this book does not appear with the books on the web page, and it appears only if I add it to all the join tables, the question is, how to display all the books in books table on the page, even if some of them don't have a record in some of the join tables?

Here is the sql select and join statement:

$sql = "SELECT DISTINCT books.title AS Title, books.date_released AS Year, books.price AS Price, authors.name AS Author FROM books

JOIN books_covers
ON books_covers.book_id = books.id

JOIN covers
ON covers.id = books_covers.cover_id



JOIN books_authors
ON books_authors.book_id = books.id

JOIN authors
ON authors.id = books_authors.author_id



JOIN books_locations
ON books_locations.book_id = books.id

JOIN locations
ON locations.id = books_locations.location_id



JOIN books_languages
ON books_languages.book_id = books.id
JOIN languages
ON languages.id = books_languages.language_id

";

[Derokorian]

What you are looking for is LEFT JOIN, which mean join the table on the right to the table on the left, as opposed to join the two tables where they are equal.

Example:

CREATE TABLE test1 (
   id int not null auto_increment primary key,
   name varchar(20)
);

CREATE TABLE test2 (
   id int not null auto_increment primary key,
   test1_id int not null,
   detail varchar(100)
);

INSERT INTO test1 (name) values ('Bob'),('Jane'),('Jack');
INSERT INTO test2 (test1_id, detail) values (1,'27yo Male'),(2, '20yo Female');

SELECT t1.name, t2.detail FROM test1 t1 LEFT JOIN test2 t2 ON t1.id=t2.test1_id;

[CodingLearner]

What you are looking for is LEFT JOIN, which mean join the table on the right to the table on the left, as opposed to join the two tables where they are equal.

Example:

CREATE TABLE test1 (
   id int not null auto_increment primary key,
   name varchar(20)
);

CREATE TABLE test2 (
   id int not null auto_increment primary key,
   test1_id int not null,
   detail varchar(100)
);

INSERT INTO test1 (name) values ('Bob'),('Jane'),('Jack');
INSERT INTO test2 (test1_id, detail) values (1,'27yo Male'),(2, '20yo Female');

SELECT t1.name, t2.detail FROM test1 t1 LEFT JOIN test2 t2 ON t1.id=t2.test1_id;

Thanks I added what you said and it worked:
LEFT JOIN products_sunglasses_brands
ON products_sunglasses_brands.product_id = products.id

LEFT JOIN sunglasses_brands
ON sunglasses_brands.id = products_sunglasses_brands.sunglasses_brand_id
You did great to me thank you so much.

[CodingLearner]

What you are looking for is LEFT JOIN, which mean join the table on the right to the table on the left, as opposed to join the two tables where they are equal.

Example:

CREATE TABLE test1 (
   id int not null auto_increment primary key,
   name varchar(20)
);

CREATE TABLE test2 (
   id int not null auto_increment primary key,
   test1_id int not null,
   detail varchar(100)
);

INSERT INTO test1 (name) values ('Bob'),('Jane'),('Jack');
INSERT INTO test2 (test1_id, detail) values (1,'27yo Male'),(2, '20yo Female');

SELECT t1.name, t2.detail FROM test1 t1 LEFT JOIN test2 t2 ON t1.id=t2.test1_id;

Adding LEFT JOIN to the tables join clause really allowed displaying all the products, but the new problem occured is that this solution has made the id of all products disappear from the fetched results.

Take a look, these are is the records array fetched from mysql database while the words LEFT JOIN removed from joining clause:

[1] => Array ( [id] => 1 [brand] => Coleman [model] => 6509-C1 Sunglasses [condition] => New [availability] => [overview] => [description] => )

And this is when LEFT JOIN added to the joining clause:

[3] => Array ( [id] => [brand] => Coleman [model] => 6509-C1 Sunglasses [condition] => New [availability] => [overview] => [description] => )

As you know is necessary to include the id of all the products with their arrays as all operations in the application depend on it.
Do you know how to resolve this please?

[CodingLearner]

I have resolved it by using:

$sql = "SELECT DISTINCT products.id, products.brand, products.model, products.price, products.category, products.availability, products.overview, products.label_image, products.brand FROM products

Instead of:

$sql = "SELECT * FROM products

It seems that using SELECT * will exclude selecting the id column in products table when using LEFT JOIN in the joining causes, at least that what happens in my application.

Thank you Derokorian again.

[Derokorian]

Actually what was happening is you were selecting all columns from all the tables in the joins. Columns that have the same name will cause ambiguity, and so when the driver loads the rows into the array the last column with that name is used (aka the last table to be joined with a column having that name). For this reason it is recommended that you always list specifically the columns with you expect to retrieve from a query.