Professional Web Applications Themes

quick join question - MySQL

I have 3 tables. One is a collections table that gets searched. It contains collections of posts from another table. Fulltext index on the subject. Second one is a lot bigger table that has the posts. Then I have a third table that has each collection id matched with the post id. each collection id could have anywhere from 1-500 posts connected to it. In the posts table there is 2 columns with numbers. I would like to do a fulltext search on the collections table and get the results from it but also get the sum of the 2 ...

  1. #1

    Default quick join question

    I have 3 tables. One is a collections table that gets searched. It
    contains collections of posts from another table. Fulltext index on
    the subject. Second one is a lot bigger table that has the posts.
    Then I have a third table that has each collection id matched with the
    post id. each collection id could have anywhere from 1-500 posts
    connected to it. In the posts table there is 2 columns with numbers.
    I would like to do a fulltext search on the collections table and get
    the results from it but also get the sum of the 2 number columns. If
    I do 2 queries and use the ids from the collections and do a join with
    the third table and the posts table it is very fast. Thing is people
    search the collections and get like 500 results at a time. and to do
    500 extra queries does not work. Here is a sample of the 2 queries.

    SELECT `id`,`subject`,`numfiles`,`groups`,`size`,`from`,` date`,`nfo`
    FROM temp.collections WHERE match (subject) against ('$find' in
    boolean mode) order by `date` desc

    SELECT sum(num),sum(`max`) FROM mainsubjects, collectionpartids where
    cid = $cid and fid = id

    Extremest Guest

  2. #2

    Default Re: quick join question

    On 21 May, 06:15, Extremest <net> wrote: 

    You have not constructed these queries in such a way that anyone can
    tell what field is in what table and the table names in the queries do
    not match those in the explanation.

    Please make it clearer what field in what table relates to what field
    in another table and which table is which.

    Then, we'll have a go at helping.

    Captain Guest

  3. #3

    Default Re: quick join question

    ok see if this is better. The cid is for collections id and the fid
    is for mainsubjects id.

    SELECT
    `collections`.`id`,`collections`.`subject`,`collec tions`.`numfiles`,`collections`.`groups`,`collecti ons`.`size`,`collections`.`from`,`collections`.`da te`,`collections`.`nfo`
    FROM temp.collections WHERE match (`collections`.`subject`) against
    ('$find' in
    boolean mode) order by `collections`.`date` desc

    SELECT sum(mainsubjects.num),sum(mainsubjects.`max`) FROM
    mainsubjects, collectionpartids where
    collectionpartids.cid = $cid and collectionpartids.fid =
    mainsubjects.id

    Extremest Guest

  4. #4

    Default Re: quick join question

    I've come up with this query but it is very different on times. some
    are done in like a sec or less and some take like 50 secs.

    SELECT
    `collections`.`id`,`collections`.`subject`,`collec tions`.`numfiles`,`collections`.`groups`,`collecti ons`.`size`,`collections`.`from`,`collections`.`da te`,`collections`.`nfo`,sum(mainsubjects.num),sum( mainsubjects.`max`)
    FROM temp.collections, temp.mainsubjects, collectionpartids WHERE
    match (`collections`.`subject`) against ('dvdr' in
    boolean mode) and collections.id = collectionpartids.cid and
    collectionpartids.fid = mainsubjects.id group by collections.id order
    by `collections`.`date` desc

    Extremest Guest

Similar Threads

  1. sql join question
    By Scott in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: March 2nd, 06:09 PM
  2. join question - maybe map
    By Gary Stainburn in forum PERL Beginners
    Replies: 1
    Last Post: October 16th, 09:48 AM
  3. Join Tables Real Quick - I cant get the syntax rigth.
    By A in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 21st, 03:00 PM
  4. inner join question
    By John in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 3rd, 06:46 AM
  5. SQL Join question
    By Bob Barrows in forum ASP Database
    Replies: 0
    Last Post: July 24th, 02:08 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