Need Help with Subquery

jjfletch

Hi, I'm trying to create a subquery, but the following isn't working, and I'm not sure why:

SELECT * FROM pending WHERE username = (SELECT username FROM ratings);

I've even tried it without the parentheses, but I get the following error message regardless:

ERROR 1064: You have an error in your SQL syntax near 'SELECT username FROM ratings)' at line 1

Can anyone help?

onion2k

Sub selects are only available in MySQL 4.1 and above. What version are you using?

jjfletch

Bummer .... I'm using 3.23.48.

I've tried to use the following query:

SELECT ratings.username,pending.username FROM ratings,pending WHERE ratings.username="jjfjunk" OR pending.username="jjfjunk";

But this returns an empty set, even though I know that the username exists in the ratings table.

Am I missing something?

Roger_Ramjet

I think with this query, even though it looks like you'll find a record if the name exists in either table, infact you'll only get a resultset when the name exists in both tables.

What field are you joining on? Or rather, is there an index in common? A straight fix rather depends on how your data is organised.

If you want to select all the entries in the pending table for a user who exists in the ratings table then this should do it


"SELECT ratings.*, pending.* FROM ratings LEFT JOIN pending ON ratings.username = pending.username WHERE ratings.username = 'jjfjunk'"

Should give you all the data from ratings if the user is in there. You will get all the data from pending if the user is also in there. If not, then you will get all the field from pending, but with a Null value in them. If there is more than one entry in pending then you will get multpile rows returned, each with the ratings data repeated.