 | |
Problem with a LEFT JOIN query...
comp.databases.mysql comp.databases.mysql
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Problem with a LEFT JOIN query... -
03-04-2010
, 09:26 AM
#2
| |||
| |||
|
|
SELECT album.albumid, album.name, album_cover.coverid FROM album LEFT JOIN album_cover ON album.albumid = album_cover.albumid; That query returns something similar to this: albumid name coverid --------- ------------ ------------ 1 Moon NULL 2 Money 5 3 Jailbreak NULL 4 Shuttle 98 4 Shuttle 124 4 Shuttle 298 5 Woman 254 What i'm trying to do, and this is the part where i'm stuck, is to return something similar to this: albumid name coverid filename --------- ------------ ------------ -------------- 1 Moon NULL blank.gif 2 Money 5 covermeny.jpg 3 Jailbreak NULL blank.gif 4 Shuttle 98 shuttle.jpg 4 Shuttle 124 back_shuttle.jpg 4 Shuttle 298 front_shuttle.jpg 5 Woman 254 cover_back.jpg In other words, when the coverid is NULL get the default value for the filename. |
defined for the column -- but that's a pointless default anyway, since if
there's no cover, there's no row in that table.
Quote:
|
I'm not sure if this is possible. If it is, how do i use the table "cover" with the column "filename"? |
, IF (cover.filename IS NULL, 'blank.gif', cover.filename)
FROM album LEFT JOIN album_cover ON album.albumid =
album_cover.albumid
LEFT JOIN cover ON cover.coverid = album_cover.coverid
;
#3
| |||
| |||
|
|
Hello, I'm not sure if the query i'm trying to do is possible. I've done half the work but i'm stuck on the last part. Let me exlain. I have 3 tables. One table that contains information about albums (album), another table that contains the album covers (cover) and one table named: album_cover Basically, one album contains n covers and one cover can be in more then one album, therefore the need for the last table; album_cover. So far so good. In resume, the table "album" goes like this: albumid smallint unsigned not null primary key, name varchar(50) not null, datereleased date The table "cover": coverid smallint unsigned not null primary key, filename varchar(50) not null DEFAULT 'blank.gif' If you notice, the default value for the filename column is a blank image. Just in case there is no cover, i would like to show a default one, therefore the blank.gif and.. table album_cover: albumid smallint unsigned not null, coverid smallint unsigned not null, primary key(albumid, coverid) Some albums don't have a cover yet and when they don't have a cover, i will use the default value "blank.gif" The query that returns the albums including those that don't contain covers looks like this: SELECT album.albumid, album.name, album_cover.coverid FROM album LEFT JOIN album_cover ON album.albumid = album_cover.albumid; That query returns something similar to this: albumid * name * * * * coverid --------- * * ------------ * * ------------ 1 * * * * * * Moon * * * * NULL 2 * * * * * *Money * * * 5 3 * * * * * *Jailbreak * *NULL 4 * * * * * *Shuttle * * * 98 4 * * * * * *Shuttle * * * 124 4 * * * * * *Shuttle * * * 298 5 * * * * * *Woman * * *254 What i'm trying to do, and this is the part where i'm stuck, is to return something similar to this: albumid * name * * * *coverid * * * *filename --------- * * ------------ * *------------ * * *-------------- 1 * * * * * * Moon * * * *NULL * * * * *blank.gif 2 * * * * * *Money * * * 5 * * * * * * * *covermeny.jpg 3 * * * * * *Jailbreak * *NULL * * * * *blank.gif 4 * * * * * *Shuttle * * *98 * * * * * * * shuttle.jpg 4 * * * * * *Shuttle * * *124 * * * * * * back_shuttle.jpg 4 * * * * * *Shuttle * * *298 * * * * * * front_shuttle.jpg 5 * * * * * *Woman * * 254 * * * * * * cover_back.jpg In other words, when the coverid is NULL get the default value for the filename. I'm not sure if this is possible. If it is, how do i use the table "cover" with the column "filename"? I know this could easily be done in PHP... if the coverid is NULL, then show a default image. But, i prefer to do everyting that is possible in the query and use PHP for those impossible things that cannot be done in a MySQL query. Thanks in advance for all your help. Marco |
#4
| |||
| |||
|
|
On Mar 4, 3:26*pm, SM <servandomont... (AT) gmail (DOT) com> wrote: Hello, I'm not sure if the query i'm trying to do is possible. I've done half the work but i'm stuck on the last part. Let me exlain. I have 3 tables. One table that contains information about albums (album), another table that contains the album covers (cover) and one table named: album_cover Basically, one album contains n covers and one cover can be in more then one album, therefore the need for the last table; album_cover. So far so good. In resume, the table "album" goes like this: albumid smallint unsigned not null primary key, name varchar(50) not null, datereleased date The table "cover": coverid smallint unsigned not null primary key, filename varchar(50) not null DEFAULT 'blank.gif' If you notice, the default value for the filename column is a blank image. Just in case there is no cover, i would like to show a default one, therefore the blank.gif and.. table album_cover: albumid smallint unsigned not null, coverid smallint unsigned not null, primary key(albumid, coverid) Some albums don't have a cover yet and when they don't have a cover, i will use the default value "blank.gif" The query that returns the albums including those that don't contain covers looks like this: SELECT album.albumid, album.name, album_cover.coverid FROM album LEFT JOIN album_cover ON album.albumid = album_cover.albumid; That query returns something similar to this: albumid * name * * * * coverid --------- * * ------------ * * ------------ 1 * * * * * * Moon * * * * NULL 2 * * * * * *Money * * * 5 3 * * * * * *Jailbreak * *NULL 4 * * * * * *Shuttle * * * 98 4 * * * * * *Shuttle * * * 124 4 * * * * * *Shuttle * * * 298 5 * * * * * *Woman * * *254 What i'm trying to do, and this is the part where i'm stuck, is to return something similar to this: albumid * name * * * *coverid * * * *filename --------- * * ------------ * *------------ * * *-------------- 1 * * * * * * Moon * * * *NULL * * * * *blank.gif 2 * * * * * *Money * * * 5 * * * * * * * *covermeny.jpg 3 * * * * * *Jailbreak * *NULL * * * * *blank..gif 4 * * * * * *Shuttle * * *98 * * * * * * * shuttle.jpg 4 * * * * * *Shuttle * * *124 * * * * * *back_shuttle.jpg 4 * * * * * *Shuttle * * *298 * * * * * *front_shuttle.jpg 5 * * * * * *Woman * * 254 * * * * * * cover_back.jpg In other words, when the coverid is NULL get the default value for the filename. I'm not sure if this is possible. If it is, how do i use the table "cover" with the column "filename"? I know this could easily be done in PHP... if the coverid is NULL, then show a default image. But, i prefer to do everyting that is possible in the query and use PHP for those impossible things that cannot be done in a MySQL query. Thanks in advance for all your help. Marco COALESCE(filename,'blank.gif') |
a query, the function coalesce and how to do more then one LEFT
join ... i guess i still have a lot to learn... thanks
#5
| |||
| |||
|
|
On Mar 4, 11:27*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote: On Mar 4, 3:26*pm, SM <servandomont... (AT) gmail (DOT) com> wrote: Hello, I'm not sure if the query i'm trying to do is possible. I've done half the work but i'm stuck on the last part. Let me exlain. I have 3 tables. One table that contains information about albums (album), another table that contains the album covers (cover) and one table named: album_cover Basically, one album contains n covers and one cover can be in more then one album, therefore the need for the last table; album_cover. So far so good. In resume, the table "album" goes like this: albumid smallint unsigned not null primary key, name varchar(50) not null, datereleased date The table "cover": coverid smallint unsigned not null primary key, filename varchar(50) not null DEFAULT 'blank.gif' If you notice, the default value for the filename column is a blank image. Just in case there is no cover, i would like to show a default one, therefore the blank.gif and.. table album_cover: albumid smallint unsigned not null, coverid smallint unsigned not null, primary key(albumid, coverid) Some albums don't have a cover yet and when they don't have a cover, i will use the default value "blank.gif" The query that returns the albums including those that don't contain covers looks like this: SELECT album.albumid, album.name, album_cover.coverid FROM album LEFT JOIN album_cover ON album.albumid = album_cover.albumid; That query returns something similar to this: albumid * name * * * * coverid --------- * * ------------ * * ------------ 1 * * * * * * Moon * * * * NULL 2 * * * * * *Money * * * 5 3 * * * * * *Jailbreak * *NULL 4 * * * * * *Shuttle * * * 98 4 * * * * * *Shuttle * * * 124 4 * * * * * *Shuttle * * * 298 5 * * * * * *Woman * * *254 What i'm trying to do, and this is the part where i'm stuck, is to return something similar to this: albumid * name * * * *coverid * * * *filename --------- * * ------------ * *------------ * * *-------------- 1 * * * * * * Moon * * * *NULL * * * * *blank.gif 2 * * * * * *Money * * * 5 * * * * * * * *covermeny.jpg 3 * * * * * *Jailbreak * *NULL * * * * *blank.gif 4 * * * * * *Shuttle * * *98 * * * * * * * shuttle.jpg 4 * * * * * *Shuttle * * *124 * * * * * * back_shuttle.jpg 4 * * * * * *Shuttle * * *298 * * * * * * front_shuttle.jpg 5 * * * * * *Woman * * 254 * * * * * * cover_back.jpg In other words, when the coverid is NULL get the default value for the filename. I'm not sure if this is possible. If it is, how do i use the table "cover" with the column "filename"? I know this could easily be done in PHP... if the coverid is NULL, then show a default image. But, i prefer to do everyting that is possible in the query and use PHP for those impossible things that cannot be done in a MySQL query. Thanks in advance for all your help. Marco COALESCE(filename,'blank.gif') thanks guys. today i've learned how to insert an if/else condition in a query, the function coalesce and how to do more then one LEFT join ... i guess i still have a lot to learn... thanks |
how do you get the value of filename in php if i have something like
COALESCE(filename,'blank.gif') ...
....
while($row = $result->fetch_assoc()){
echo $row['filename']; //doesn't work
#6
| |||
| |||
|
|
On Mar 4, 12:13*pm, SM <servandomont... (AT) gmail (DOT) com> wrote: On Mar 4, 11:27*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote: On Mar 4, 3:26*pm, SM <servandomont... (AT) gmail (DOT) com> wrote: Hello, I'm not sure if the query i'm trying to do is possible. I've done half the work but i'm stuck on the last part. Let me exlain. I have 3 tables. One table that contains information about albums (album), another table that contains the album covers (cover) and one table named: album_cover Basically, one album contains n covers and one cover can be in more then one album, therefore the need for the last table; album_cover. So far so good. In resume, the table "album" goes like this: albumid smallint unsigned not null primary key, name varchar(50) not null, datereleased date The table "cover": coverid smallint unsigned not null primary key, filename varchar(50) not null DEFAULT 'blank.gif' If you notice, the default value for the filename column is a blank image. Just in case there is no cover, i would like to show a default one, therefore the blank.gif and.. table album_cover: albumid smallint unsigned not null, coverid smallint unsigned not null, primary key(albumid, coverid) Some albums don't have a cover yet and when they don't have a cover, i will use the default value "blank.gif" The query that returns the albums including those that don't contain covers looks like this: SELECT album.albumid, album.name, album_cover.coverid FROM album LEFT JOIN album_cover ON album.albumid = album_cover.albumid; That query returns something similar to this: albumid * name * * * * coverid --------- * * ------------ * * ------------ 1 * * * * * * Moon * * * * NULL 2 * * * * * *Money * * * 5 3 * * * * * *Jailbreak * *NULL 4 * * * * * *Shuttle * * * 98 4 * * * * * *Shuttle * * * 124 4 * * * * * *Shuttle * * * 298 5 * * * * * *Woman * * *254 What i'm trying to do, and this is the part where i'm stuck, is to return something similar to this: albumid * name * * * *coverid * * * *filename --------- * * ------------ * *------------ * * *-------------- 1 * * * * * * Moon * * * *NULL * * * * *blank.gif 2 * * * * * *Money * * * 5 * * * * * * * *covermeny.jpg 3 * * * * * *Jailbreak * *NULL * * * * *blank.gif 4 * * * * * *Shuttle * * *98 * * * * * * * shuttle.jpg 4 * * * * * *Shuttle * * *124 * * * * ** back_shuttle.jpg 4 * * * * * *Shuttle * * *298 * * * * ** front_shuttle.jpg 5 * * * * * *Woman * * 254 * * * * * * cover_back.jpg In other words, when the coverid is NULL get the default value for the filename. I'm not sure if this is possible. If it is, how do i use the table "cover" with the column "filename"? I know this could easily be done in PHP... if the coverid is NULL, then show a default image. But, i prefer to do everyting that is possible in the query and use PHP for those impossible things that cannot be done in a MySQL query. Thanks in advance for all your help. Marco COALESCE(filename,'blank.gif') thanks guys. today i've learned how to insert an if/else condition in a query, the function coalesce and how to do more then one LEFT join ... i guess i still have a lot to learn... thanks one more question on this subject: how do you get the value of filename in php if i have something like COALESCE(filename,'blank.gif') ... ... while($row = $result->fetch_assoc()){ * * * * * * * * echo $row['filename']; //doesn't work |
and also with columns... hehehe
SELECT album.name, album.subname, coalesce(cover.filename,
'blank.gif') as filename
...
while($row = $result->fetch_assoc()){
$row['filename']
}
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.