Preserved Topic: MySQL query involving a join and many to many connections.

Okay, I have a fully optimized database structure. For the sake of simplicity here are the fields from each table that are relevant to the question:

Event : event_id, title_id



Title : title_id, title



Sponsor : sponsor_id, sponsor



Event_Sponsor : event_id, sponsor_id

What I want is a query that will return a row containing event_id and title for each event that has a sponsor from a pre-determined list (set by users). I am pretty sure that joins will not suffice (although I am not clear about using joins on many to many junction tables like Event_Sponsor) because we only want 1 row max for each event. It seems subselects would solve the problem quite nicely, but I have a feeling with the right WHERE clause I can solve the problem. Here's my query so far:

SELECT Event.event_id, Title.title FROM Event LEFT JOIN Title USING (title_id) WHERE ???;

I plan to build a where clause out of a loop that checks the boolean array of user-selected sponsors, but I'm not sure how to phrase it. It stands to reason that I can only compare field names from the actual tables being used for output, otherwise how would it know what row to censor? Yet, each occurrence of a given event_id in the junction table Event_Sponsor to see if it matches one of the given sponsors.

The more I think about it, the more it seems sub selects would be necessary for this behavior, but perhaps someone knows a clever trick. That is if I explained it sufficiently well for you to understand. If not, well it's easy to have PHP parse the query for only qualifying results, just less efficient. Any ideas?



-jiblet

I'm not totally sure I tracked that whole discussion, but I think I understand you to say you have a bit vector (is that what you mean by boolean array?) representing user selections. Linear likes bit vectors. Bitwise AND is your friend. Represent the sponsor ID as a bit vector also, with just one bit set. The position scheme needs to match the scheme of your other bit vector (obviously). Then your where clause boils down to the bitwise AND of user selections and sponsor ID, and MySQL can do bitwise AND.

I think that works, if I understood correctly.

Intriguing concept. I have no practical experience with bit vectors, but I think you are right! I already have implemented the solution with this code:

for($i = 0; $i < mysql_num_rows($result); $i++) {

		$row = mysql_fetch_array($result);

		$found = false;

		$es = mysql_query("SELECT sponsor_id FROM Event_Sponsor WHERE event_id = {$row['event_id']}");

		if (mysql_num_rows($es) == 0) {

			$found = true;

		}

		else {

			$j = 0;

			while($j < mysql_numrows($es) && !$found) {

				if(${'sponsor'.$j}) $found = true;

				$j++;

			}

		}

		if($found) {

			echo ("<tr><td align=\"right\"><input type=\"checkbox\" name=\"delete$i\" value=\"1\"></td>\n

<td nowrap>{$row['title']}</td>\n

<td nowrap>{$row['start_time']}</td>\n

<td><input type=\"button\" value=\"Edit\" onClick=\"document.forms[0].elements[1].value='{$row['event_id']}';document.forms[0].submit();\"></td></tr>\n");

		}

	}

Your solution seems like it might be more efficient, hard to say since I am pretty sure it would require a database query for each iteration of the loop to build the bit vector for each item. I will probably just stick with the solution I have for now, but answer me this:

how do you specify a value as binary. I understand how an integer translates to binary and how to build an appropriate value (ie. a loop for $i = 0 to bit-length, if true then value+= 2 ^ $i), but can you specify the bits directly as binary?

-jiblet

[This message has been edited by jiblet (edited 05-09-2001).]

Yeah, integer or long integer type works just fine.

So for example here's your sponsor table

ID name
1 foo
2 bar
4 baz
8 quux

User selects foo and quux on the form, giving 9 as a selector.
So in binary, 9 is 1001, 1 is 0001, and 8 is 1000
so bitwise AND of 9 and 1 looks like
1001
0001
____
0001 which evaluates true.

The expression in your where clause could be the bitwise AND, since MySQL can do that operation. For a set of userchoices, you get back one row for each matching sponsor.

Yes. If MySQL implemented subselects it would be easy, but because it doesn't I doubt there is a way to get it into one query. The reason is because it has to check multiple rows in the Event_Sponsor table to see if any of them match up with the selected sponsors. This means that a new bit vector would need to be generated for each event_id in the table. I could use a join of some sort so that it would all be in one table, but it needs to return one and only one row for each event. If I joined, it would return several rows for a single event if it had more than one sponsor. Even if I can return only one row per event, it still would reutnr only the FIRST sponsor, which might not necessarily be one on the list.

It boils down to a question of efficiency. Either way I have to run a loop to check each item in the inital query to see if it has one of the sponsors from the selected set. Without bit vectors for each iteration I have to run another loop that checks each sponsor value for the particular event. With bit vectors I have to generate a new bit vector by means of a mysql query to compare to Event_Sponsor, then compare it to the user selection bit vector. This would seem to be faster, but I don't think that putting the bitwise comparison in a mysql query could be of any use, because it changes with every loop.

-jiblet