September 5th, 2000, 09:41 AM
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?
September 6th, 2000, 06:54 AM
September 7th, 2000, 05:09 AM
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?
September 7th, 2000, 05:48 AM
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