#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Cardiff
    Posts
    9
    Rep Power
    0
    Please help with my problem

    I have a main table - call it DOCUMENT. I then have other tables, eg. TOPIC, AUTHOR, TARGETAUDIENCE, which can have multiple rows all referencing the same main DOCUMENT row.

    Is there any way of writing a single SELECT statement that extracts all of the relevant rows from the child tables, but only returns the minimum number of rows (i.e. the number of rows it returns will equal the number of relevant rows from one of the child tables). Currently, my SELECT statement returns a long list of results, using every combination of columns possible.

    Do I have to resort to multiple SELECT statements, one on each table?
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    19
    Rep Power
    0
    - Why do you name tables unrelated if there are references to DOCUMENT?
    - Use join of these tables. See http://www.mysql.com/documentation/m...p?section=JOIN
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Cardiff
    Posts
    9
    Rep Power
    0
    Sorry - I guess 'unrelated' wasn't the correct phrasing... I am joining the tables, using a primary key in the main table - e.g. SELECT a.author, b.title, c.title FROM a,b,c WHERE b.aid = a.id AND c.aid = a.id

    Now, if I have five results from table b, and five results from table c, then the above statement actually gives a 5*5 = 25 row result. Is there some kind of JOIN or DISTINCT terminology I can use to only get five rows in the results?

    Thanks.

  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    19
    Rep Power
    0
    SELECT max(a.author), b.title, max(c.title) FROM a,b,c WHERE b.aid = a.id AND c.aid = a.id group by b.title

Similar Threads

  1. Selecting from more than two tables
    By JonnyThunder in forum MySQL Help
    Replies: 9
    Last Post: February 4th, 2004, 06:24 AM
  2. selecting distring records across multiple tables
    By kubicon in forum MySQL Help
    Replies: 3
    Last Post: January 9th, 2004, 05:41 PM
  3. selecting common cols in multiple tables
    By phprook in forum MySQL Help
    Replies: 8
    Last Post: December 10th, 2003, 01:48 PM
  4. Replies: 4
    Last Post: November 19th, 2003, 04:15 PM
  5. Replies: 1
    Last Post: September 27th, 2003, 10:03 AM

IMN logo majestic logo threadwatch logo seochat tools logo