WHat is wrong with this SQL statement

[hosker]

Here is my SQL statement:

 

$sql2 = "SELECT username from clp_user WHERE ide != 1 AND username NOT IN (SELECT * from test_picks WHERE t_id = 3)";
	$res2 = mysql_query($sql2) or die (mysql_error());
		while($row2 = mysql_fetch_array($res2))
{
	echo $row2[username] . '<br />';
};

 

I have two tables within my DB. I want to select all the usernames from the users table that are not in the test picks table that have a t_id = 3. This statement is returning all of the usernames in my users table, why is that?

[kicken]

Your subquery should only select a single column, not *.  However, you could probably do what you want better using a left join query instead.

 

I took a guess that the column in test_picks that your matching with the username column in clp_user is also named username

SELECT c.username 
FROM clp_user c
LEFT JOIN test_picks t ON t.username=c.username AND t.t_id=3
WHERE 
ide != 1 
AND t.username IS NULL

 

[hosker]

I am not very familiar with joins within MySQL. However, I got the code to produce the results I needed by changing "AND username" to "AND user". However I am trying to run a 2ndquery with the first queries results and am having no luck. Here is the following code so far:

 

$sql2 = "SELECT username from clp_user WHERE ide != 1 AND user NOT IN (SELECT * from test_picks WHERE t_id = 3)";
	$res2 = mysql_query($sql2) or die (mysql_error());
		while($row2 = mysql_fetch_array($res2))
{
	//echo $row2[username] . '<br />';
	$sql = "SELECT * from test_picks WHERE t_id = 2 AND user = $row2[username]";
	$res1 = mysql_query($sql1) or die (mysql_error());
	while($row1 = mysql_fetch_array($res1))
	{
		echo $row1[user] . '<br />';
	};

};

 

I echoed out the user to make sure that I was pulling the right rows.

 

The output I am getting is: Unknown column 'user' in 'IN/ALL/ANY subquery'

 

I do have a column user in the test_picks table.

[hosker]

The code that I changed gave me the right result once, but has given me errors since. Would a join statement make what I am trying to do easier? Here are the coloumns in my DB tables:

 

clp_users:

 

ide username password role status fname lname dob email secquestion secanswer createdby creationtime

 

 

test_picks:

 

id t_id tournament user player backup timestamp email

 

I am trying to pull all the users who did not make a pick for the current tournament, go back to the previous week and grab that particular users backup pick. After I do that I want to insert that into my database with particular parameters. I know how to do the insert I just need help in creating the correct set of SQL statements. Any and all help is appreciated. Thanks.