SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Bringing back unique rows - group by clause?

    Hi there,

    I have the following statement

    SELECT * FROM messages,threads where messages.userid = 3 order by msgid desc

    where there is a one to many relationship regarding threads to messages. The query is supposed to bring back the message details (such as msg_message ) and the thread title that the message relates to. Just using that query brings back repeats of the same rows so i'm guessing a group by clause is required. However the following:

    SELECT * FROM messages,threads where messages.userid = 3 group by msgid order by msgid desc

    brings back different messages but they all have the same thread_title, regardless of the thread they were posted in. My structure is as follows:

    CREATE TABLE threads (
    threadid bigint(20) NOT NULL auto_increment,
    thread_title varchar(255) NOT NULL default '',
    thread_date datetime NOT NULL default '0000-00-00 00:00:00',
    userid bigint(20) NOT NULL default '0',
    subcatid int(11) NOT NULL default '0',
    PRIMARY KEY (threadid)
    ) TYPE=MyISAM;

    CREATE TABLE messages (
    msgid bigint(20) NOT NULL auto_increment,
    userid int(11) NOT NULL default '0',
    msg_message text NOT NULL,
    threadid bigint(20) NOT NULL default '0',
    subcatid int(11) NOT NULL default '0',
    msg_date date NOT NULL default '0000-00-00',
    PRIMARY KEY (msgid)
    ) TYPE=MyISAM;

    The SQL needs to be compatible with MySQL 3.x

    Any help greatly appreciated!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, you don't want GROUP BY

    the problem with this --
    Code:
    SELECT * 
      FROM messages,threads 
     where messages.userid = 3 
    order by msgid desc
    is that you have omitted the join condition so you're getting a cross join

    you need to match the messages to the threads they belong to
    Code:
    SELECT * 
      FROM messages,threads 
     where messages.threadid = thread.threadid
       and messages.userid = 3 
    order by msgid desc
    the join is actually better written this way --
    Code:
    SELECT * 
      FROM messages 
    inner join threads 
        on messages.threadid = thread.threadid
     where messages.userid = 3 
    order by msgid desc
    for one thing, you can't inadvertently leave out the ON clause (you get a syntax error)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great thanks for the speedy reply r937 works a treat now.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •