How to write this basic inner join SQL query

Question

I have two tables:

This is my posts table:

+-----+----------------+-------------+
| ID  |   post_title   | post_status |
+-----+----------------+-------------+
| 105 | Red Shirt      | active      |
| 106 | Green Shoes    | active      |
| 107 | Blue Hat       | hidden      |
+-----+----------------+-------------+

This is my meta table:

+-----------+------+
| object_id | lang |
+-----------+------+
|       105 | en   |
|       106 | fr   |
|       107 | en   |
+-----------+------+

I am trying to write an SQL query that returns the ID's of all the posts with the post_status of active AND the lang en. With these two tables, there should only be one result (105).

I am having trouble matching the ID to object_id. Any help would be really appreciated, thank you.

Brian check an answer posted by me whether it fulfill your requirement or not — Dipak, Oct 31 '17 at 19:05

Dipak · Answer 1 · 2017-10-31T19:20:25.337

0

SELECT p.ID
FROM posts p, meta m
WHERE p.ID = m.object_id
AND p.post_status = 'active'
AND m.lang='en'

edited Oct 31 '17 at 19:20

answered Oct 31 '17 at 19:04

Dipak

2,248
4
22
55

score 0 · Answer 2 · answered Oct 31 '17 at 19:08

0

Join the two tables and then specify the conditions:

SELECT posts.ID
FROM posts
INNER JOIN meta ON meta.object_id = posts.id
WHERE posts.post_status = 'active'
  AND meta.lang = 'en'

answered Oct 31 '17 at 19:08

Racil Hilan

24,690
13
50
55

score 0 · Answer 3 · answered Oct 31 '17 at 19:53

Given that you only want those rows that have records in both tables, you want an INNER JOIN. There is a good post about different join types here: SQL JOIN and different types of JOINs

For your query, you want the following:

SELECT
    PostTbl.ID -- The column(s) you want to select
FROM
    `posts` AS PostTbl -- I prefer aliasing every table so I keep the habit when self joining
INNER JOIN -- Since we want only the rows with records in both tables
    `meta` AS MetaTbl
    ON PostTbl.ID = MetaTbl.object_id -- The conditions on which we want the tables to join
WHERE
    PostTbl.post_status = 'active' -- Post is 'active'
    AND MetaTbl.lang = 'en'; -- Language is 'en'

How to write this basic inner join SQL query

3 Answers3