www.webdeveloper.com
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Apr 2012
    Posts
    9

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

    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

    ";

  2. #2
    Join Date
    Oct 2010
    Location
    Ohio
    Posts
    233
    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:

    Code:
    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;

  3. #3
    Join Date
    Apr 2012
    Posts
    9

    Smile It worked.

    Quote Originally Posted by Derokorian View Post
    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:

    Code:
    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.

  4. #4
    Join Date
    Apr 2012
    Posts
    9

    This solution resolved the problem but created another one!

    Quote Originally Posted by Derokorian View Post
    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:

    Code:
    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:
    Code:
    [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:
    Code:
    [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?

  5. #5
    Join Date
    Apr 2012
    Posts
    9

    Resolved too!

    I have resolved it by using:

    Code:
    $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:

    Code:
    $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.

  6. #6
    Join Date
    Oct 2010
    Location
    Ohio
    Posts
    233
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles