CODEWITHSUNDEEP
postgresqlselectconditional-statements
flower58
flower58

Reputation: 687

fetching a table with another tables "conditions"

I have two tables like this:

Table Name: users

emx  |   userid
---------------
1    |   1
2    |   2

and another table called bodies

id   |   emx   |   text
--------------------------
1    |   1     |   Hello
2    |   2     |   How are you?

As you can see, bodies table has emx which is id numbers of users table. Now, when i want to fetch message that contains Hello i just search it on bodies and get the emx numbers and after that i fetch users table with these emx numbers. So, i am doing 2 sql queries to find it.

So, all i want to do is make this happen in 1 SQL query.

I tried some queries which is not correct and also i tried JOIN too. No luck yet. I just want to fetch users table with message contains 'Hello' in bodies table.

Note: I am using PostgreSQL 9.1.3.

Any idea / help is appreciated.

Upvotes: 0

Views: 100

Answers (2)

vyegorov
vyegorov

Reputation: 22865

  1. Read docs on how to join tables.
  2. Try this:
    SELECT u.emx, u.userid, b.id, b.text
      FROM bodies b
      JOIN users u USING (emx)
     WHERE b.text ~ 'Hello';

Upvotes: 2

Dan D.
Dan D.

Reputation: 74645

This is how I'd do the join. I've left out the exact containment test.

SELECT users.userid 
FROM users JOIN bodies ON (users.emx = bodies.emx) 
WHERE ⌜true if bodies.text contains ?⌟

Upvotes: 1

Related Questions