FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL Query help





richard270384
Hi,

Not sure if this is possible.

Let's say I have a query which returns the following data:
Code:

credit  lyric_id  artist_id  lyric_title                     artist_name 
lyric    855       51          Love Of My Life            Queen
lyric    1573     51          My Fairy King               Queen
lyric    1623     51          My Melancholy Blues     Queen
lyric    853       51          Seaside Rendezvous     Queen
music  1579     51          Seven Seas of Rhye...   Queen
lyric    1579     51          Seven Seas of Rhye...   Queen


As you can see, the last two rows are the same song, with different 'credits'.

Is it possible in MySQL to somehow group by lyric_id, and create a new field that would have all the 'credits' in it for that song, rather than returning 2 rows for the same song?

The above dataset has been picked up from 3 tables:
Code:

Table: LYRIC_CREDITS
Fields: lyric_id, link_id, credit

Table: LYRIC
Fields: lyric_id, lyric_title, artist_id

Table: ARTIST
Fields: artist_id, artist_name


Using this query:

Code:

SELECT credit, lyric_credits.lyric_id, lyric.artist_id, lyric_title, artist_name
FROM lyric_credits
INNER JOIN lyric ON lyric.lyric_id = lyric_credits.lyric_id
INNER JOIN artist ON artist.artist_id = lyric.artist_id
WHERE link_id =18
ORDER BY artist_name, lyric_title



Hope that makes sense. Please let me know if it doesn't.

Cheers.
kv
Code:

SELECT lyric_id, lyric_title, artist_id, GROUP_CONCAT(credit) FROM LYRIC lyric, ARTIST artist, LYRIC_CREDITS credits  where artist.artist_id = lyric.artist_id and lyric.lyric_id = credits.lyric_id group by (lyric.lyric_id)
richard270384
Fantastic.

THanks mate!
Related topics
mysql/php help please
Help with mysql query
MySQL connection
mysql gives the wrong result [solved]
Error connecting to mysql ; please help
mySQL Query/PHP code - get the highest value...
What is wrong with this MySQL query?
[Edit] mySQL Count
whats wrong with this mysql query?
MySQL Query Cache
what is wrong with this query? Please please help!
MySQL query help
mysql query based on 2 fields using 3 different tables
PHP and Javascript Issue handling data from a mysql query
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.