LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   SQL query question (https://www.linuxquestions.org/questions/programming-9/sql-query-question-496896/)

Kamikazee 10-30-2006 08:20 AM

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
FROM messages;

Which is my problem, it gets distinct of both columns combined. Can anyone help?

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
FROM messages, topics
WHERE topics.ID_TOPIC = messages.ID_TOPIC;

which does sweetfa until i can get distinct for ID_TOPIC only.

Any DB query gurus out there?

kev82 10-30-2006 10:39 AM

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.

95se 10-31-2006 10:27 AM

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
FROM messages
GROUP BY ID_TOPIC;

Should work in atleast MySQL 5.

Edit: Cleaned up.


All times are GMT -5. The time now is 02:11 AM.