-1

I cannot get multiple rows of the Posts.body column to show up in the subquery. I DO want subqueries and I haven't figured out a way around this MySQL restraint. Please let me know if you need more information. 

SELECT profile_picture, body, post_date, filename, username FROM Posts, Users 
WHERE Posts.IDUser = Users.IDUser 
  AND Posts.body LIKE (SELECT Posts.body FROM Posts WHERE Posts.IDUser = (SELECT Users.IDUser FROM Users WHERE Users.username = 'noah'))
ORDER BY `Posts`.`post_date` DESC;
jeroen
  • 91,079
  • 21
  • 114
  • 132
user2793987
  • 199
  • 1
  • 2
  • 15

2 Answers2

2

It's not really clear what the problem is exactly, but from what I can see it seems you need to use IN instead of LIKE:

SELECT profile_picture, body, post_date, filename, username FROM Posts, Users 
WHERE Posts.IDUser = Users.IDUser 
  AND Posts.body IN (SELECT Posts.body FROM Posts WHERE Posts.IDUser = (SELECT Users.IDUser FROM Users WHERE Users.username = 'noah'))
                 ^^ here
ORDER BY `Posts`.`post_date` DESC;

On the other hand the inner SELECTs seem unnecessary as you are already joining Posts and Users so you can probably simplify it a lot using just a join.

What exactly are you trying to select?

jeroen
  • 91,079
  • 21
  • 114
  • 132
  • I just tried that and it returns all of the rows. Is that supposed to happen? I want to find similar text in the body column from the current user. – user2793987 Jun 07 '14 at 03:43
  • @user2793987 *Similar text* is pretty broad, do you mean to search for certain words or do you really want to analyze similaries? I doubt you can do the latter easily in sql. – jeroen Jun 07 '14 at 03:47
  • Analyze similarities or really just wildcard it. – user2793987 Jun 07 '14 at 03:48
0

Any time you are joining a table to itself, or using it in a subquery, you have to alias it.

So your inner query should be something like

(
  SELECT p.body 
  FROM Posts p 
  WHERE p.IDUser = something
)

I find it pretty hard to understand what you're trying to do with this query. I have a feeling it could be greatly simplified but I'm not sure what data you are after.

James
  • 20,957
  • 5
  • 26
  • 41
  • I want to find all similar text from a specific user that they have posted in the body column of the table. This is my json data: http://shipstudent.com/complaint_desk/similarPosts.php?username=noah – user2793987 Jun 07 '14 at 03:46
  • I essentially just want to wildcard the whole "body" column. – user2793987 Jun 07 '14 at 03:51
  • You can find a word or phrase within a string easily using LIKE, but finding keywords or selecting records where the difference falls within a certain tolerance is decidedly NOT easy. See [this](http://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity) for more details – James Jun 07 '14 at 03:55
  • Hmm, so there is no way of obtaining multiple records out of a subquery? – user2793987 Jun 07 '14 at 03:58