[SOLVED] Subquery

[The Little Guy]

How can I get this subquery to work?

 

SELECT * FROM users, videos WHERE videos.id = '%s'
AND
videos.owner = users.user AND (SELECT COUNT(id) as totalVids FROM videos) LIMIT 1

 

I don't get an error, but when I do a print_r(), it returns everything, except for what is in the subquery. How can I get this to work?

[fenway]

Define "work".

[aschk]

Ok, just evaluate that last part in your head for a minute "...AND (SELECT COUNT(id) as totalVids FROM videos)..."

I have just one question when that subquery returns the COUNT(), i.e. number of records from the videos table, and you AND it onto your current statement what do you think it will look like. e.g. substitute a number in there to represent a potential result:

... AND 3 ...

 

Define that logic... " I want blah, blah, blah from some table where blah = something AND 3"

[The Little Guy]

Define "work".

 

By "work", I mean that the query returns all the results for the first select statement but not the second one.

 

so... the non-working version would look something like this (Some results removed due to length):

 

(user/password::table=users, title/tags/id::table=videos)

user                 password                 title                 tags                 id 
ryan                 8934j4ididd9          good movie            movie awesome       19b3ae5975ac

 

That is the results it returns, but it didn't return the subquery in the results, which would be the number: 0+

 

I read about subqueries at MySQL.com, but it didn't help me.

[fenway]

By "work", I mean that the query returns all the results for the first select statement but not the second one.

...

That is the results it returns, but it didn't return the subquery in the results, which would be the number: 0+

Well, ahem, you didn't put the subquery in your SELECT column list, so how could it?

 

You'll have to write a dependent subquery that get evaluated for each id.

[The Little Guy]

sorry, I am not sure what you mean.

[aschk]

Right, so explain what you want in plain english then.

Something like:

 

"I want the number of videos for EVERY user",

or

"I want ALL the video titles that Ryan likes".

 

What is the joining clause between the 2 tables? As in, how do you relate/map users to videos? You need an interim table by the looks of it. Unless of course i'm mistaken and the id field in the videos as being the id of the user? Is that the case?

[The Little Guy]

I want the video information (videos table), the users information (users table), and the total number of videos the user has uploaded (COUNT() on videos table).

[The Little Guy]

I GOT IT!

 

SELECT * FROM 
(SELECT COUNT(id) AS totalVids FROM videos WHERE owner = 'ryan') AS totalV,videos, users
WHERE
videos.owner = users.user AND
users.user = 'ryan' AND
videos.id = 'f7430204d23d' LIMIT 1