CODEWITHSUNDEEP
phpmysqlsql
Sebastian
Sebastian

Reputation: 3628

MySQL query encompassing two fields

My two tables, with only the relevant values included, are:

xx_users contains all users, including the current user and the poster.

In mock code, what I am trying to achieve is:

SELECT * FROM xx_posts WHERE poster location = user location

Given the crossover between the two tables, how would I approach this?

Upvotes: 0

Views: 60

Answers (4)

StevieG
StevieG

Reputation: 8709

If I've understoood the question correctly, you want to see all posts which were posted by a user who shares alocation with the current user (or another given user). In which case:

SELECT p.* 
FROM xx_posts p 
INNER JOIN xx_users poster ON p.user_id = poster.user_id 
INNER JOIN xx_users currentuser ON u1.location = u2.location
WHERE currentuser.user_id = <the id of the user you're interested in>

Upvotes: 1

Kermit
Kermit

Reputation: 34055

To get the intersection of two sets, use INNER JOIN:

SELECT a.user_id, a.name, a.location, b.post
FROM xx_posts b
INNER JOIN xx_users a ON a.user_id = b.user_id

See it in action

To filter based on location, add:

WHERE a.location = 'Alaska'

It's also best practice to specify a column list, and avoid using SELECT *.

INNER JOIN

Upvotes: 0

BlackCat
BlackCat

Reputation: 521

Do u want this?

SELECT p.post,p.user_id,...
FROM xx_posts as p
INNER JOIN xx_users as u
ON p.user_id=u.user_id
WHERE p.location = u.location

http://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 0

berty
berty

Reputation: 2206

If I well understood your question, the solution can be something like :

SELECT * from posts INNER JOIN users ON users.user_id = posts.user_id WHERE users.location = 'the location'

Upvotes: 4

Related Questions