SQL query question
Hey, Im having trouble with a single query
I cant really explain this that well. Except for: Im converting an old yabbse database to phpbb2. Except all i have is the .sql file. Im converting the tables in my own mySQL server offline. The tables Im having trouble with is the topics. Due to yabbse not storing the topic subject with the topic id. (Topic subject is stored with the message/post) . Sooo. long story short. I need to extract the first topic subject based on destinct topic ids in the message table. Code:
SELECT distinct ID_TOPIC, subject My final select query looks like this atm, Code:
SELECT DISTINCT messages.ID_TOPIC, topics.ID_BOARD, messages.subject, messages.ID_MEMBER, messages.posterTime, topics.numViews, topics.numReplies Any DB query gurus out there? |
I would have to see the table to understand exactly what's going on, but it sounds like to me you have repeats of ID_TOPIC and you want to select the subject of the first instance of an ID_TOPIC.
how about select ID_TOPIC, subject from messages group by ID_TOPIC Not sure which subject you would get though. Hopefully the first one! If that doesn't work there is some kind of group_concat function so you can get all the subjects and then use some string functions to pick out the one you want. |
Kind of a hack-job way of doing it but,
Code:
SELECT ID_TOPIC, LEFT(GROUP_CONCAT(subject SEPARATOR '<<<%%%>>>'), IF(COUNT(ID_TOPIC)>1, INSTR(GROUP_CONCAT(subject SEPARATOR '<<<%%%>>>'), '<<<%%%>>>') - 1, LENGTH(GROUP_CONCAT(subject)))) AS topic Edit: Cleaned up. |
All times are GMT -5. The time now is 02:11 AM. |