LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
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.

Notices



Reply
 
Search this Thread
Old 10-30-2006, 09:20 AM   #1
Kamikazee
Member
 
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.
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?
 
Old 10-30-2006, 11:39 AM   #2
kev82
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
95se
Member
 
Registered: Apr 2002
Location: Windsor, ON, CA
Distribution: Ubuntu
Posts: 740

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

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


Reply


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 03:35 PM.

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