CODEWITHSUNDEEP
phpmysqlsqlsql-serversql-server-2008
user2967888
user2967888

Reputation: 1449

How to retrieve the status of users has same interest Mysql query issue

I am learning MYsql and php. Now stuck with this weird problem in mysql. I have two tables soc_users_interest and soc_status. Please see the table structure below.

----------                -----------
soc_status                soc_users_interest
----------                ------------
status_ID                 soc_interest_id (Auto increment)
status                    userID
userID                    Interest

i am trying to list all status of users which has same interests of the user (User who use the application). I made a query and it's not returning the right results. Here's the query i wrote which doesn't work. 

 SELECT soc_status.statusID, soc_status.status FROM soc_status
WHERE soc_status.userID =

      (SELECT  
  soc_users_interest.userID as firstUser , 
  soc_users_interest.interest as firstUserInterest , 

  secondUser.userID as secondUser, 
  secondUser.interest as secondUserInterest 

 FROM soc_users_interest JOIN
  soc_users_interest secondUser 
     ON soc_users_interest.interest = soc_users_interest.interest) WHERE soc_users_interests.userID = "23445";

I am getting this error -> `[2016-07-09 20:53:26] [21000][1241] Operand should contain 1 column(s)`

How can i list all status of users which has same interests of the user (User who use the application) ? Any solutions to this problem ?. Thanks for helping ;)

Upvotes: 1

Views: 65

Answers (3)

starshine531
starshine531

Reputation: 591

Something like this should give you what you need. Though I don't have your tables, so I have not tested it.

SELECT  
  soc_users_interest.userID as firstUser , 
  soc_users_interest.interest as firstUserInterest , 
  secondUser.userID as secondUser, 
  secondUser.interest as secondUserInterest ,
  soc_status.statusID, soc_status.status

 FROM soc_users_interest, soc_users_interest secondUser,soc_status
 WHERE  
 firstUser.interest = secondUser.interest
 AND soc_status.userID=secondUser.userID
 AND firstUser.userID = "23445";

Upvotes: 0

Szue
Szue

Reputation: 61

Just a question. Why do you need 2 tables. Combined this data into one. If you feel that you cannot have 1 table, use a table JOIN with the correct number of columns WHERE tbl1.Userid = tbl2.Userid

Upvotes: 0

Prachi Verma
Prachi Verma

Reputation: 101

SELECT soc2.userID, soc2.status, int.interest  FROM soc_status soc1
join soc_status soc2 on soc1.user_id=soc2.user_id and soc1.user_id="23445"
join soc_users_interest int on soc1.user_id=int.user_id

Upvotes: 0

Related Questions