This query fails: I am trying to SELECT columns from different tables, I know left-join, inner join etc. But this is different.
SELECT stream.user_id, stream.verb, stream.object_id
(SELECT users.id, users.uid FROM users
(SELECT profiles.user_id, profiles.name, profiles.picture, profiles.link FROM profiles
WHERE profiles.user_id = users.uid)
WHERE users.id = "1")
FROM stream
INNER JOIN follows ON stream.user_id = follows.following_user
WHERE follows.user_id = "0"
ORDER BY stream.stream_date DESC LIMIT 10;
You’re missing commas (first one is at the sub query)
have errors in your sub-queries.
you’re referencing a table (follows) which you never actually retrieve from
What exactly are you trying to accomplish? If you can tell me that, and what fields you’re actually trying to retrieve, I can help you straighten out the queries…
OK, so item #3 was wrong (I’m doing a ton all at once). First two are definitely wrong - what fields are you looking for from users and profiles? Those are the ones that are hosing you…
Again, that was only part of the problem. This query is syntactically correct, so it’ll run, but the sub-query really doesn’t make sense in this context. Why would you look for a user_id of 0 in the main and user_id of 1 in the sub-query.
SELECT stream.user_id
, stream.verb
, stream.object_id
, SQ.id
, SQ.uid
, SQ.user_id
, SQ.name
, SQ.picture
, SQ.link
FROM stream
INNER JOIN follows ON stream.user_id = follows.following_user
INNER JOIN (SELECT users.id
, users.uid
, profiles.user_id
, profiles.name
, profiles.picture
, profiles.link
FROM users
INNER JOIN profiles ON profiles.user_id = users.uid
WHERE users.id = "1") SQ
WHERE follows.user_id = "0"
ORDER BY stream.stream_date
DESC LIMIT 10;