CODEWITHSUNDEEP
mysqlsql
Siva Charan
Siva Charan

Reputation: 105

mysql query for creating a facebook like newsfeed

I have three tables users,friends and newsfeed in my mysql database called "test". the values in those tables are

users table values...

id    |    username    |    password
-------------------------------------
  1   |     user1      |      pass1
-------------------------------------
  2   |     user2      |      pass2
-------------------------------------
  3   |     user3      |      pass3

friends table values

id    |    friend_from    |    friend_to    |    status
--------------------------------------------------------
  1   |      user1        |       user2     |       1
--------------------------------------------------------
  2   |      user2        |       user3     |       1
--------------------------------------------------------
  3   |      user3        |       user1     |       1

newsfeed table values

id    |    username    |    post
----------------------------------
  1   |    user1       |    post1
----------------------------------
  2   |    user2       |    post2
----------------------------------
  3   |    user3       |    post3

Now I want to fetch all the data of the friends of user1 from newsfeed table.for that I wrote query as follows

SELECT * FROM newsfeed WHERE friends.friend_from=newsfeed.username AND friends.friend_to='user1' AND friends.status=1 OR friends.friend_from='user1'AND friends.friend_to=newsfeed.username AND friends.status=1

Now i am expecting the results as follows

user2    post2
user3    post3

But I am getting the following result...

#1054 - Unknown column 'friends.friend_from' in 'where clause'

What is the reason...

Upvotes: 0

Views: 748

Answers (1)

Shaggy
Shaggy

Reputation: 6796

As you have it, your SQL doesn't know what friends is, you need to include it in the list of tables you're selecting from:

SELECT * FROM newsfeed,friends WHERE friends.friend_from=newsfeed.username AND friends.friend_to='user1' AND friends.status=1 OR friends.friend_from='user1' AND friends.friend_to=newsfeed.username AND friends.status=1

You can also assign shorthand names to the tables to make them easier to reference elsewhere in your SQL, like so:

SELECT * FROM newsfeed n,friends f WHERE f.friend_from=n.username AND f.friend_to='user1' AND f.status=1 OR f.friend_from='user1' AND f.friend_to=n.username AND f.status=1

Upvotes: 1

Related Questions