...

View Full Version : Resolved Display records with null values



conware
10-15-2011, 02:23 PM
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:



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

BubikolRamios
10-15-2011, 03:43 PM
don't mix implicit and explicit joins (think that are the terms), which is what you are doing now.




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:



left join t1 on t1.id = t2.id and t1.lang = 'en'

red part is basicaly equals wheret t1.lang = 'en'

conware
10-15-2011, 07:47 PM
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


video_id
video_views_id
video_title
video_file
video_content


content table


content_id
content_title
content_content


ore make them like this:

content_table


content_id
content_title
content_content


video_table


video_id
video_views_id
video_file


track_table


id
video_id
content_id

BubikolRamios
10-15-2011, 08:57 PM
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 !




content_table


content_id
content_title
content_content


video_table


video_id
video_views_id
video_file


track_table


id
content_id


Disregard coloring above, this all should be one single table



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


member_id
video_id
count_of_wievs(default 0)

conware
10-15-2011, 11:10 PM
Ones again thank you verry much BubikolRamios.
I'll keep your info in mind when I continue my database design.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum