how to requery a query?

murtoz

Hi,

I need to select some rows from one table, and then select some more rows from a different table based on this first selection (where id is not listed in first query). I know the sql to select from one table where id is not in other table, but I can not figure out how to refer to the first query in the second one, as it is a query not a table.
Any suggestions?

Thanks!

ultraslacker

What exactly are you trying to do? Give us the query + subquery, even if your dbms doesn't support subqueries.

You can get around mysql's lack of subquery support sometimes with HAVING - worst case is using a temporary table.

gonsman

If your database supports subqueries, I am pretty sure that you can name you tables with aliases and reference current values from a subquery using the aliased name. Example:

SELECT  a.column1, 
		a.column2,
			(SELECT b.column1, b.column2
			FROM table1 AS b
			WHERE b.column1 = a.column1)
		FROM table1 AS a

The first query uses the alias 'a' for table1 and the second query uses the alias 'b' for table1. This way, whatever value a.column1 has can be used in the subquery if table1 is aliased differently in the subquery. Someone correct me if I am wrong.

-Sean

murtoz

Ended up using an array:
$included = "";
$sql = "SELECT member.id AS uid FROM member, count_me_in WHERE member.id = count_me_in.id";
$result = mysql_query($sql);
if (!$result) {
echo "<p>Error Performing Query: ". mysql_error() ."</p>";
exit();
} else {
while ($myrow = mysql_fetch_array($result)) {
if ($included != "") {
$included .= ", ";
}
$included .= "'". $myrow["uid"] ."'";
}
}

$emails = array ();
$sql = "SELECT id, email FROM member WHERE member.id NOT IN ($included)";
$result = mysql_query($sql);
if (!$result) {
echo "<p>Error Performing Query: ". mysql_error() ."</p>";
exit();
} else {
while ($myrow = mysql_fetch_array($result)) {
$emails[] = $myrow["email"];
}
}

keith73

how to requery a query?

Hi,

I need to select some rows from one table, and then select some more rows from a different table based on this first selection (where id is not listed in first query). I know the sql to select from one table where id is not in other table, but I can not figure out how to refer to the first query in the second one, as it is a query not a table.
Any suggestions?

Thanks!

Try using LEFT JOIN
http://www.mysql.com/doc/en/JOIN.html

$query = "SELECT * FROM table1 LEFT JOIN table2 ON table1.id1 = table2.id2";

keith

murtoz

Hi Keith,

I tried that, using the following sql:
select tips.id, tips.tipname from tips left join events on tips.id=events.tip where events.tip is null.

This gave me all the rows from tips of which the id was not present in events.tip.

However, i only want to take the rows in events in consideration where events.type=1 and events.user='x'

If i add this to the query above and make:
select tips.id, tips.tipname from tips left join events on tips.id=events.tip where events.tip is null and events.type=1 and events.user='x' it returns no rows whatsoever. Ergo: incorrect query.
I could not figure out what the query should be that compares events.tip for all the rows in events where user='x' and type=1 with tips.id... is this possible?

Thanks:-)

nnichols

so add the - "events.type=1 and events.user='x'" as a HAVING clause instead of a WHERE clause, then it will work.

nnichols

SELECT tips.id, tips.tipname
FROM tips LEFT JOIN events ON tips.id=events.tip
WHERE events.tip IS NULL
HAVING events.type=1 AND events.user='x'

murtoz

thanks nnichols:-)

I tried your suggestion:
SELECT tips.id, tips.tipname, events.*
FROM tips
LEFT JOIN events

ON tips.id = events.tip
WHERE events.tip IS NULL
HAVING events.type = 1 AND events.user = 'x';

unfortunately that query does not return any rows, whereas
SELECT DISTINCT *
FROM events
WHERE type=1
AND user='x'
gives me 6 rows. There are 50 rows in the tips table so the first query should return 44 rows...

Can it be a problem that some of the rows in events have the tip field not filled in?

Thanks:-)

nnichols

Do you have an instant message client installed (MSN, AOL, ICQ), if so what is your handle?

If not send me a PM with you email.

nnichols

I've figured out why it doesn't return any rows -

SELECT tips.id, tips.tipname, events.* 
FROM tips 
LEFT JOIN events 
ON tips.id = events.tip 
WHERE events.tip IS NULL 
HAVING events.type = 1 AND events.user = 'x';

The WHERE clause tells mysql only to return rows from tips whose id does not appear in events. You then ask it to only do this where type is 1 and user is 'x' - which it can't do because if the id does not exist in events, it can't check for other values in the events table for rows that don't exist.

It's not a very clear explanation but hopefully you can understand what I'm saying.

murtoz

Nick,

Yeah, that's what i sorta figured... So the solution with the array and two queries is the best/only one?

Thanks:-)
Michiel

nnichols

Yes.

Good luck with getting it all sorted.

Nick

murtoz

Swene,

Are you sure you're replying to this thread?
:-)