Need help with SQL Statement (VERY COMPLICATED)

[zerohaze]

I am not actually sure if i can write a single SQL statement to carry out the function i need, so before looking at the SQL code which is extremely long and complicated and is dynamic in ASP here is a brief description of what im trying to achieve.

Hope you understand this...

Without mentioning the site or service; I have a system that randomly selects (Website A) and (Website B) out of a pool of thousands of websites. Currently my System works using THREE seperate SQL queries that are executed independantly one after another. As each sql statement is built dynamicly based upon criteria retrieved from the previous SQL statements. T

1st SQL Statement: Selects Criteria belonging to my account.
2nd SQL Statement: Selects (Website A) randomly from a pool of thousands of websites based upon criteria retrieved from the first SQL Statement.
3rd SQL Statement: Selects (Website B) randomly from a pool of thousands of websites based upon criteria retrieved from the SECOND SQL statement. (In other words when finding at least one Website B it checks values relating to this record against values found for Website A in the previous SQL Statement)

[b]Heres my Problem:[/b]

I have a database that contains several thousand websites. Each website has certain rules relating to each other. During the second SQL Statement I select a random website that we will call (Website A) based upon criteria from the 1st SQL statement. There a numerous websites that could have been returned but i have limited the 2nd SQL statement to just 1 result (and this is the problem as the record it chooses may cause the third SQL statement to retrieve no results). The third SQL statement is than initiated and looks for a website we will call (Website B) randomly from thousands however this website must match criteria relating to website A found in the prevoius (2nd) SQL Statement.

If there is no (Website B) relating to (Website A) and passing all the rules involved. then it obviously it does not return a result.

The system than tells the user that we could only find one website. (It needed to find 2).

The problem here is that the system may have actually been able to find (Website B) if it selected a different (Website A) in the 2nd Query. Thats why i need to make the queries into just one query.

This SQL query therefore needs to be able to ask itself (If I select (Website A) can i than select a (Website B which must meet Website A's criteria) if so its ok, otherwise Select a different Website A, and keep on looping itself until it exhausts all possible combinations.)

How can i make this one query when both queries are returning a result from the same Table based upon Different conditions. A Union wont work as it will still return a result for Website A even if there is no Website B.


R u confused yet? .... here is a sample SQL code below that is generated by ASP in three seperate SQL executions.

--------------------------------

Select * From mem_websites,members,mem_webcatagory, exc_subcatagory,exc_maincatagory where ((mws_id = mwc_mwsid) and (mwc_escid = esc_id) and (esc_mainid = emc_id)) and (mem_id != '154' and mws_userid = mem_id) and (mem_active = 2) and (mem_level = 1 or mem_level = 2 or mem_level = 3) and (mws_group = '2') and NOT EXISTS (Select * From mul_pending where (mul_pending.mpe_web1id = '147' and ((mul_pending.mpe_web2id = mem_websites.mws_id)or(mul_pending.mpe_web3id = mem_websites.mws_id))) or (mul_pending.mpe_web2id = '147' and ((mul_pending.mpe_web1id = mem_websites.mws_id)or(mul_pending.mpe_web3id = mem_websites.mws_id))) or (mul_pending.mpe_web3id = '147' and ((mul_pending.mpe_web2id = mem_websites.mws_id)or(mul_pending.mpe_web1id = mem_websites.mws_id)))) and NOT EXISTS (Select * From mul_declined where (mul_declined.mde_web1id = '147' and ((mul_declined.mde_web2id = mem_websites.mws_id)or(mul_declined.mde_web3id = mem_websites.mws_id))) or (mul_declined.mde_web2id = '147' and ((mul_declined.mde_web1id = mem_websites.mws_id)or(mul_declined.mde_web3id = mem_websites.mws_id))) or (mul_declined.mde_web3id = '147' and ((mul_declined.mde_web2id = mem_websites.mws_id)or(mul_declined.mde_web1id = mem_websites.mws_id)))) and NOT EXISTS (Select * From mul_completed where (mul_completed.mco_web1id = '147' and ((mul_completed.mco_web2id = mem_websites.mws_id)or(mul_completed.mco_web3id = mem_websites.mws_id))) or (mul_completed.mco_web2id = '147' and ((mul_completed.mco_web1id = mem_websites.mws_id)or(mul_completed.mco_web3id = mem_websites.mws_id))) or (mul_completed.mco_web3id = '147' and ((mul_completed.mco_web2id = mem_websites.mws_id)or(mul_completed.mco_web1id = mem_websites.mws_id)))) and mws_Cat5 = 2 and (esc_mainID = '1' or esc_mainID = '2' or esc_mainID = '3' or esc_mainID = '4' or esc_mainID = '5' or esc_mainID = '6' or esc_mainID = '7' or esc_mainID = '8' or esc_mainID = '9' or esc_mainID = '10' or esc_mainID = '11' or esc_mainID = '12' or esc_mainID = '13' or esc_mainID = '14' or esc_mainID = '15' or esc_mainID = '16' or esc_mainID = '17' or esc_mainID = '18' or esc_mainID = '19' or esc_mainID = '20') ORDER BY RAND() Limit 1

[b]The code above is the second SQL statement. As you can see in first line i Select All fields, those fields are then used to dynaically write the third sql command which is basically the same as above just with different conditions based upon the result it found. My problem is that if no record meets those conditions i want it to go back and choose another website in the 2nd statement, so i need to combine them into one. Any IDeas?[/b]

[zerohaze]

Actually here is a much simplier question

Based upon the SQL seen above is it possible to add a condition in which the SQL checks the condition not against the returned result but against all other non-returned results in the table.

For example if that SQL statement returned 1 website meeting those conditions set out of 100 that also met the conditions, Than I than want it to check a further condition based upon the values just returned for the 1 result found against all other records in the table that did not meet the condition. If there is not a single record found in the table that passes this condition that it selects another out of the 100 that were found and retry the second condition again.

Is this possible?

[zerohaze]

Whoops, the answer is staring right at me  ::)

Turns out i just need to add another correlated subquery with an EXISTS condition to check if the returned result's values will enable a further record to be found.

[fenway]

There must be a simpler way to write that query, but if you've got it working....

[hvle]

There is always a neat, logictis, and straight forward solution to your problem. 

If you write query like that, it will confuse you and the sql itself.  Your co-worker probably has no clue what you're doing, and he might not even ask you for explaination.