469,600 Members | 2,225 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

using database contents as column names

Hi,

I have two tables I'm trying to join up, and would like to know if the
following is possible:

table1 :
SELECT * from mediatype

+-------------+---------+
| mediatypeid | type |
+-------------+---------+
| 1 | MP3 |
| 2 | ra_low |
| 3 | ra_med |
| 4 | ra_high |
+-------------+---------+

Table 2:
SELECT trackid, mediatypeid, url from media

+---------+-------------+----------------+
| trackid | mediatypeid | url |
+---------+-------------+----------------+
| 1 | 1 | mymp3.mp3 |
| 1 | 2 | myothermp3.mp3 |
| 1 | 3 | busted.mp3 |
| 1 | 4 | newmp3.mp3 |
+---------+-------------+----------------+
What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Is there a way to do this completly in SQL? or i could employ PHP to
help out..

Any help greatly appreciated!

Cheers,
Guy
Jul 19 '05 #1
3 1100
SR
On 3 Feb 2004 09:45:14 -0800, Guy Bowden wrote:
Hi, [...] What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Hi,

basically you want to obtain a pivot table. AFAIK, no SQL dialect except M$
Access's supports it *directly*, but there's something you can do, anyway:

First issue this query:

select concat(", max(if(mediatypeid=", mediatypeid, ", url, Null)) AS ",
type) from mediatype;

This will result in:

, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high

Then use php to create the actual query string:

select trackid
, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high
from media group by trackid;

et voila...

Jul 19 '05 #2
SR
On 3 Feb 2004 09:45:14 -0800, Guy Bowden wrote:
Hi, [...] What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Hi,

basically you want to obtain a pivot table. AFAIK, no SQL dialect except M$
Access's supports it *directly*, but there's something you can do, anyway:

First issue this query:

select concat(", max(if(mediatypeid=", mediatypeid, ", url, Null)) AS ",
type) from mediatype;

This will result in:

, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high

Then use php to create the actual query string:

select trackid
, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high
from media group by trackid;

et voila...

Jul 19 '05 #3
SR
On 3 Feb 2004 09:45:14 -0800, Guy Bowden wrote:
Hi, [...] What I'd like to be able to get out of these two tables is a result
like this :

+---------|-----------|----------------|------------|------------+
| trackid | MP3 | ra_low | ra_med | ra_high |
+---------|-----------|----------------|------------|------------+
| 1 | mymp3.mp3 | myothermp3.mp3 | busted.mp3 | newmp3.mp3 |
+---------|-----------|----------------|------------|------------+

Hi,

basically you want to obtain a pivot table. AFAIK, no SQL dialect except M$
Access's supports it *directly*, but there's something you can do, anyway:

First issue this query:

select concat(", max(if(mediatypeid=", mediatypeid, ", url, Null)) AS ",
type) from mediatype;

This will result in:

, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high

Then use php to create the actual query string:

select trackid
, max(if(mediatypeid=1, url, Null)) AS mp3
, max(if(mediatypeid=2, url, Null)) AS ra_low
, max(if(mediatypeid=3, url, Null)) AS ra_med
, max(if(mediatypeid=4, url, Null)) AS ra_high
from media group by trackid;

et voila...

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by rlrcstr | last post: by
2 posts views Thread by David C. Barber | last post: by
10 posts views Thread by shsandeep | last post: by
4 posts views Thread by eeb4u | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.