mysql subquery help

LucidParody

Does anyone know a way to gain the same advantages as a subquery (mysql 4.1)? I have 4.0 which doesn't support subqueries. If I could subquery, my problem would be fixed easily.

Basically, I am trying to combine two tables that have the same fields and run a query from that combined format. Here is the basic statement:

				SELECT
				ROUND( AVG( ( a.rank + a.ballots ) * 
				( ( LOG10(b.powerrank) / 3 ) )) + ( COUNT(a.name) * 3 ), 3 ) as  total,
				COUNT(a.name) as num,
				a.name,
				a.school,
				a.tourny,
				a.side,
				a.team,
				b.id
				FROM ranks_atty a, ranks_tourny b
				WHERE a.tourny=b.id
				GROUP BY a.name
				ORDER BY total DESC LIMIT 30

I have two tables: ranks_witness and rank_atty. The structure of both tables is identical:

i need to first combine those two tables into one table then that table would fit into the above query instead of the "rank_atty" table. i dont know if i'm being clear, but that's the basic idea. Any ideas how I can do that?

nemonoman

This was my workaround with MySQL v 3

Run the subselect funcion on one table, selecting a foreign key. Include this with an "IN" statement. the subselect function produces the selection of foreign keys in parens, like (NULL, 2, 3)

A NULL is automatically added to handle a return of zero rows from the subselect ("WHERE id IN ()" is an error,
"WHERE id IN (NULL)" is OK).

Use it like this:

$thequery="
SELECT *
FROM tableone
WHERE tableone.ID
IN ".
subselect("
SELECT tableoneID
FROM table2
WHERE table2.attribute=$myvalue
");

$result=mysql_query($thequery);

function subselect($query) {
//connect to database before running this
$result = mysql_query($query);
$selectstring = "(NULL";
while ($row = mysql_fetch_array($result)) {
$selectstring .= ", $row[0]";
}
$selectstring .= ")";
return $selectstring;
}

LucidParody

I don't think I'm doing this correctly. Here is the statement I used:

		$selected = 	subselect("SELECT
				ROUND( AVG( ( a.rank + a.ballots ) * 
				( ( LOG10(b.powerrank) / 3 ) )) + ( COUNT(a.name) * 3 ), 3 ) as total,
				COUNT(a.name) as num,
				a.name,
				a.school,
				a.tourny,
				a.side,
				a.team,
				b.id
				FROM ranks_witness a, ranks_tourny b
				WHERE a.tourny=b.id
				$andYear
				$andTourny
				GROUP BY a.name
				ORDER BY total DESC LIMIT 30");

	$query = "	SELECT
			ROUND( AVG( ( a.rank + a.ballots ) * 
			( ( LOG10(b.powerrank) / 3 ) )) + ( COUNT(a.name) * 3 ), 3 ) as total,
			COUNT(a.name) as num,
			a.name,
			a.school,
			a.tourny,
			a.side,
			a.team,
			b.id
			FROM ranks_atty a, ranks_tourny b
			WHERE a.tourny=b.id
			$andYear
			$andTourny
			IN " . $selected
			. "GROUP BY a.name
			ORDER BY total DESC LIMIT 30";

it's not returning anything.

nemonoman

Not surprised. Try something like:
$selected = subselect("SELECT
b.id
FROM ranks_tourny b
WHERE 1
$andYear
$andTourny
GROUP BY b.id
");

Then

query = " SELECT
ROUND( AVG( ( a.rank + a.ballots )
( ( LOG10(b.powerrank) / 3 ) )) + ( COUNT(a.name) 3 ), 3 ) as total,
COUNT(a.name) as num,
a.name,
a.school,
a.tourny,
a.side,
a.team,
b.id
FROM ranks_atty a, ranks_tourny b
WHERE 1
$andYear
$andTourny
ad a.tourny IN " . $selected
. "GROUP BY a.name
ORDER BY total DESC LIMIT 30";