Professional Web Applications Themes

SQL join query help - PHP Development

I have written a forum and am using the following query to search in it: $query="select topics.tid,f_messages.messid from f_messages left join topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)"; I trimmed it a bit to save space here (the last two are just strings). It works fine, but it's returning more than once the same topics.tid I want it to return only unique topic id's. distinct doesn't work. I think it has to do with the f_messages.tid = topics.tid as it isn't using distinct there. I tried returning distinct(f_messages.tid) instead but again, no help! This is using mysql. Re-structuring ...

  1. #1

    Default SQL join query help

    I have written a forum and am using the following query to search in it:

    $query="select topics.tid,f_messages.messid from f_messages left join
    topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)";

    I trimmed it a bit to save space here (the last two are just strings).

    It works fine, but it's returning more than once the same topics.tid

    I want it to return only unique topic id's.

    distinct doesn't work. I think it has to do with the f_messages.tid =
    topics.tid as it isn't using distinct there.

    I tried returning distinct(f_messages.tid) instead but again, no help!

    This is using mysql. Re-structuring the db isn't an option.

    Thanks for any light on this one, I can only think of dumping the tid's
    into a TEMP dbase and selecting distinct from there, although it sure is
    messy!


    --
    [email]poffsixbit.org[/email]
    SDF Public Access UNIX System - [url]http://sdf.lonestar.org[/url]
    poff Guest

  2. #2

    Default Re: SQL join query help

    In article <beqlfq$7to5m$1ID-30799.news.uni-berlin.de>, Agelmar wrote:
    > This should produce an output of something like
    > Topic ID | Message ID
    > 1 |1
    > 1 |2
    > 1 |3
    > 2 |7
    > 2 |13
    > 3 |4
    > 3 |5
    > ...
    Right, thing is, on a forum search you don't want to see the same topic a
    gazillion times.

    I want unique topic id's, which goes against my query.

    The (group by topics.tid) part seemed to reduce this, but I'm not getting
    unique (distinct) tid's.
    > unless of course you only care if *some* message in a given thread matches,
    > and you just want the first message in the thread that matches and don't
    > care about the rest...
    Yeah.
    > in this case, you can just do something like:
    >
    > $query="select topics.tid, MIN(f_messages.messid) from f_messages left join
    > topics on f_messages.tid = topics.tid GROUP BY topics.tid where
    > ($title_list) OR ($msg_list)";
    >
    > I hope this helps, I must admit I am not totally sure that I understand your
    > question, but I gave it my best and I hope this is what you're looking for.
    Thanks, this actually solved it, where above I have duplicate tid's even
    after your addition this is due to proper db duplications which shouldn't
    be there.

    Thanks!

    On a side note I had to put the GROUP BY at the end of the query to get it
    to work.

    --
    [email]poffsixbit.org[/email]
    SDF Public Access UNIX System - [url]http://sdf.lonestar.org[/url]
    poff Guest

  3. #3

    Default Re: SQL join query help

    poff <poffsixbit.org> wrote in message
    news:<slrnbh144m.e2s.poffotaku.freeshell.org>...
    >
    > I have written a forum and am using the following query to search in it:
    >
    > $query="select topics.tid,f_messages.messid from f_messages left join
    > topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)";
    >
    > I trimmed it a bit to save space here (the last two are just strings).
    >
    > It works fine, but it's returning more than once the same topics.tid
    And it should... Let's say topic number 5 has three messages in it
    (messid = 123, 135, and 148), so if you run

    SELECT topics.tid, f_messages.messid
    FROM f_messages LEFT JOIN topics
    ON f_messages.tid = topics.tid
    WHERE topics.tid = 5;

    you will get something like this back:

    +------------+-------------------+
    | topics.tid | f_messages.messid |
    +------------+-------------------+
    | 5 | 123 |
    | 5 | 135 |
    | 5 | 148 |
    +------------+-------------------+

    Remember, DISTINCT applies to an ENTIRE ROW IN THE RESULT SET,
    NOT to a single field and NOT to an entire record in the source
    table. So the three records in the result set above are NOT
    distinct and will all be returned.
    > I want it to return only unique topic id's.
    Then ask for them accordingly:

    SELECT DISTINCT tid FROM topics WHERE [your WHERE clause];

    Also, it seems to me you are trying to fetch the first (and only
    the first) message from each topic; this can be done like this:

    SELECT topics.tid AS topic, MIN(f_messages.messid) as message
    FROM f_messages LEFT JOIN topics
    ON f_messages.tid = topics.tid
    WHERE [your WHERE clause]
    GROUP BY topics.tid;

    Cheers,
    NC
    Nikolai Chuvakhin Guest

Similar Threads

  1. Question Database join query
    By iconic in forum Brainstorming Area
    Replies: 0
    Last Post: September 10th, 09:42 AM
  2. sql join query
    By dylan in forum MySQL
    Replies: 8
    Last Post: August 24th, 12:07 PM
  3. Inner Join Query
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 13th, 05:19 PM
  4. Add another join to a query
    By Conti in forum Coldfusion Database Access
    Replies: 6
    Last Post: March 20th, 06:23 PM
  5. INNER JOIN query question
    By Bosconian in forum MySQL
    Replies: 6
    Last Post: February 27th, 11:21 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139