Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts

    Display records with null values

    Hi guys.
    I ran into a little problem I currently have a list with video games.
    Some have developers and some don't, same goas for the genres.

    I used WHERE to replace the genre_id/developer_id with genre_name/developer_name.

    But the problem is now, my query only returns the games that have a genre_id ore developer_id. How do I solve this.

    Is there a way to use WHERE in the JOIN?
    So my compleet game list is displayed with the missing ids as null values?

    Currently my SQL looks like this:

    Code:
    SELECT t1.game_id, t1.game_title, GROUP_CONCAT(DISTINCT t2.genre_name ORDER BY t2.genre_name SEPARATOR ' / '), 
    GROUP_CONCAT(DISTINCT t5.dev_name ORDER BY t5.dev_name SEPARATOR ' / ')
    FROM (games t1, genre t2, developers t5)
    LEFT JOIN (track_genre t3) ON (t1.game_id = t3.game_id)
    LEFT JOIN (track_dev t4) ON (t1.game_id = t4.game_id)
    WHERE t2.genre_id = t3.genre_id AND t5.dev_id = t4.game_dev
    GROUP BY t1.game_title
    Last edited by conware; 10-15-2011 at 10:10 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,979
    Thanks
    120
    Thanked 76 Times in 76 Posts
    don't mix implicit and explicit joins (think that are the terms), which is what you are doing now.


    Code:
    SELECT 
    t1.game_id, t1.game_title, 
    GROUP_CONCAT(DISTINCT t2.genre_name ORDER BY t2.genre_name SEPARATOR ' / '), 
    GROUP_CONCAT(DISTINCT t5.dev_name ORDER BY t5.dev_name SEPARATOR ' / ')
    
    FROM games t1
    LEFT JOIN track_genre t3 ON t1.game_id = t3.game_id
    LEFT JOIN track_dev t4 ON t1.game_id = t4.game_id
    LEFT JOIN developers dev t5 on t5.dev_id = t4.game_dev
    LEFT JOIN genre t2 on t2.genre_id = t3.genre_id
    GROUP BY t1.game_title
    Did not go into details, but this is more readable and in it should do same as yours.
    Since you have all left joins and null does not appear, then mybe switch something to right join. Otherwise you can put null into join or where part , whatever.


    Is there a way to use WHERE in the JOIN?
    sure, example:

    Code:
    left join t1 on t1.id = t2.id and t1.lang = 'en'
    red part is basicaly equals wheret t1.lang = 'en'
    Last edited by BubikolRamios; 10-15-2011 at 02:51 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    conware (10-15-2011)

  • #3
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    Thanks for the explanation BubikolRamios, I think I undertand now.

    Also is it bad practice for me to have so maney JOINS?
    Because im trying to normalize my database.
    But im not sure how far I should go.

    say for example I have video table and a content table.
    Would it be better to make the tables like this:

    video table
    Code:
    video_id
    video_views_id
    video_title
    video_file
    video_content
    content table
    Code:
    content_id
    content_title
    content_content
    ore make them like this:

    content_table
    Code:
    content_id
    content_title
    content_content
    video_table
    Code:
    video_id
    video_views_id
    video_file
    track_table
    Code:
    id
    video_id
    content_id

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,979
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Quote Originally Posted by conware View Post

    Also is it bad practice for me to have so maney JOINS?

    But im not sure how far I should go.
    1. nope, as many as you need
    2.as far as it is posible.
    Google will give you zilion pros & contras to normalization
    Let mi give you two out of head:
    2.1.Normalization != speed
    From on guy 10 years on ORACLE
    2.2 Normalize always, denoramlize if/as needed

    In general do normalization as things are readable any you want produce , up top the limit columns per table. At times you will be finding yourself writing complicated queries to dig data from normalized tables, while getting them from denormalized tables would be peace of cake. That is the life !

    Quote Originally Posted by conware View Post

    content_table
    Code:
    content_id
    content_title
    content_content
    video_table
    Code:
    video_id
    video_views_id
    video_file
    track_table
    Code:
    id
    content_id
    Disregard coloring above, this all should be one single table

    Code:
    video_id (int autoinc if you fill like it)
    video_file
    video_title
    video_content
    video_views (default 0)
    Why ? Coz there will be no cell with null value !


    but if you would like to analyze which people viewed certain video then
    you would instead of last col 'video_views', invent new table

    tab video_views
    Code:
    member_id
    video_id
    count_of_wievs(default 0)
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    conware (10-15-2011)

  • #5
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    Ones again thank you verry much BubikolRamios.
    I'll keep your info in mind when I continue my database design.


  •  

    Posting Permissions

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