CODEWITHSUNDEEP
phpmysql
brian wilson
brian wilson

Reputation: 495

get mysql records when following a user

I have two tables:

[TABLE1]

+----------+-------------------+
| USERNAME | POST              |
+----------+-------------------+
| Bob      | 'Hi There'        |
| Jack     | 'Hello'           |
| Bob      | 'Today is Monday' |
+----------+-------------------+


[TABLE2]

+----------+-----------+
| USERNAME | FOLLOWING |
+----------+-----------+
| Mike     | Jack      |
| Jack     | Bob       |
| Bob      | Jack      |
| Jack     | Mike      |
+----------+-----------+

I know how to join two tables when you want one row.. but what about when you want to work out the below?

Select * from TABLE1 when TABLE1.USERNAME IS A USERNAME JACK is FOLLOWING;

Upvotes: 0

Views: 59

Answers (3)

Rituparna Kashyap
Rituparna Kashyap

Reputation: 1507

For Username Jack is following :

select TABLE1.USERNAME, TABLE1.POST 
from TABLE1, TABLE2
where 
     TABLE1.USERNAME = TABLE2.FOLLOWING 
     and
     TABLE2.USERNAME = "Jack"

For the Username who is following Jack 

select TABLE1.USERNAME, TABLE1.POST 
from TABLE1, TABLE2
where 
     TABLE1.USERNAME = TABLE2.USERNAME 
     and
     TABLE2.FOLLOWING = "JACK"

Upvotes: 4

asprin
asprin

Reputation: 9833

Does this help?

SELECT * FROM table1 t1, table2 t2
WHERE t2.username = 'Jack'
AND t1.username = t2.following

Upvotes: 0

xkeshav
xkeshav

Reputation: 54052

TRY ( assume PHP )

$username - 'Jack';
"SELECT t1.* FROM TABLE1 t1
INNER JOIN table2 t2 
ON t1.username = t2.username AND t2.following = '".$username."' ";

NOTE : is both table's column name are same then you can use USING(username) instead of ON condition

Upvotes: 0

Related Questions