sql join statement

Enclosed is an SQL dump of a small database. The goal is to display all items in table main who link to a given group in table C via the proper joins. This should be done in a single SQL statement, but may contain subqueries. Generic SQL is preferred, but the target database is MySQL 4.1.3 For example, if I want to show people in C.1, I'll get "show" in then content of all returned rows, and if I show c.2, I'll get "dont_show" as the content of the result set. Solutions that hardcode values (eg, WHERE a=1 OR a=3 OR b=1 OR b=2 OR b=3, WHERE ref_id=1 OR ref_id=2 or ref_id=4...) are NOT acceptable, as they apply only to the specific dataset, and won't work on any given dataset given the structure defined herein. Additionally UNION statements which would just do something like "(SELECT ... JOIN a ON c ...) UNION (SELECT ... JOIN b ON JOIN a ON c ...)" are not acceptable, since in essence it's 2 seperate queries with a single resultset, which makes it too dificult to scale the individual SQL statements (since I'd have to repeat all the extra instructions). To clarify: main has 2 fields - a ref_id which contains a reference number, and ref_type which dictates which lookup table to use. Entries in table a reference table c directly. Entries in table b reference entries in table a (which in turn reference table c). I'm not at all concerned with filtering based on the "content" field in any table. This is intended to be an abstract example, and the content is more of a reference to assist debugging than anything else. An acceptable solution will display all the correct rows from table main based on the value for a.ref_c (I don't really care about the content in c; joining on table C will only slow the query down). I'll give a bonus to the coder if the solution scales well (e.g., supports more tables with different links that end at the same reference point (ref_c), or does anything else which I think will help me down the line. ## Deliverables 1) Complete and fully-functional working statement(s) which meet the enclosed requirements. 2) Comments or some other description which explains how the statement(s) work, or how to otherwise use and/or modify them. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform OS Independant (as long as MySQL runs on it) MySQL 4.1.3 MyISAM table engine (if applicable) utf8 default character encoding (if applicable)