View the Most Wanted LQ Wiki articles.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 10-30-2006, 09:20 AM   #1
Registered: May 2005
Location: Aus
Distribution: SimplyMEPIS 3.3
Posts: 107

Rep: Reputation: 15
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.
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,

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?
Old 10-30-2006, 11:39 AM   #2
Senior Member
Registered: Apr 2003
Location: Lancaster, England
Distribution: Debian Etch, OS X 10.4
Posts: 1,263

Rep: Reputation: 50
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.
Old 10-31-2006, 11:27 AM   #3
Registered: Apr 2002
Location: Windsor, ON, CA
Distribution: Ubuntu
Posts: 740

Rep: Reputation: 32
Kind of a hack-job way of doing it but,
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 
Should work in atleast MySQL 5.

Edit: Cleaned up.

Last edited by 95se; 10-31-2006 at 11:29 AM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query SQL error gabsik Linux - Software 37 07-22-2006 03:34 AM
SQL query performance smaida Programming 6 06-08-2005 10:22 AM
SQL query help pls. vickr1z Programming 8 10-19-2004 12:25 AM
Massive SQL Query patpawlowski Programming 7 03-05-2004 05:24 PM
SQL Query question oulevon Programming 7 01-16-2004 02:50 AM

All times are GMT -5. The time now is 08:48 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration