Hi all, I've got a really difficult sql question: You all know that I can't use subquery's (IN), UNION en FULL OUTER JOIN. (only LEFT OUTER JOIN is supported) I'm making a search query who gives an scoring percentage. It's the same idea as some internet search engines use. (like excite) I simplified my model, because in real live there are more tables and constraints. I've got three table (simplified): TABLE_A #Aid a1 a2 TABLE B #Aid #Cid TABLE C #Cid cc (# is primary key) Relations: (A) 1---n (B) n---1 (C) Now I've got the following query's SELECT Aid, COUNT(*) FROM TABLE_A A, TABLE_B B, TABLE_C C WHERE A.Aid = B.Aid AND B.Cid = C.Cid GROUP BY Aid Asume that there is one row: Aid = 1 with count = 6 Now I make the same query three more times with each a different constraint like this: SELECT Aid, COUNT(*) FROM TABLE_A A, TABLE_B B, TABLE_C C WHERE A.Aid = B.Aid AND B.Cid = C.Cid AND C.cc = 'constraint1' GROUP BY Aid and SELECT Aid, COUNT(*) FROM TABLE_A A, TABLE_B B, TABLE_C C WHERE A.Aid = B.Aid AND B.Cid = C.Cid AND C.cc = 'constraint2' GROUP BY Aid and SELECT Aid, COUNT(*) FROM TABLE_A A, TABLE_B B, TABLE_C C WHERE A.Aid = B.Aid AND B.Cid = C.Cid AND C.cc = 'constraint3' GROUP BY Aid Asume query 2 exists and has as result Aid = 1 and count = 2 Asume query 3 exists and has as result Aid = 1 and count = 2 Asume query 4 doesn't exists The scoring percentage is now 3*2 = 6 (the maximum score for query 1 t/m 3) Or, if 6 is bigger than the count in query 1 the maximum score is the count of query 1. (query 2 + query3 + query 4) / 6 = 4/6 = 66% My question is: Is is possible to make one query out of the three above? (That's a nice question for the weekend) greetings, Emile ----------------------------------------------------------- Send a mail to [EMAIL PROTECTED] with unsubscribe mysql [EMAIL PROTECTED] in the body of the message to unsubscribe from this list.
