CODEWITHSUNDEEP
mysqljoinconditional-statements
user381800
user381800

Reputation:

MYSQL select query based on another tables entries

I have stumped on this as I am a total beginner in MySql.

Here is a the basic of how the two tables are formed

Table 1 id,product_id, product_name

Table 2 id,product_id,active

Now i know how to do a select statement to query the results from one table but when I have to involve two, I am lost. Not sure if I have to use inner join, left join etc.

So how can I return the results of the product_id from table 1 only if in table 2 is active?

Upvotes: 7

Views: 15819

Answers (2)

thevilledev
thevilledev

Reputation: 2377

You could use JOIN (as Fosco pointed out), but you can do the same thing in the WHERE clause. I've noticed that it's a bit more intuitive method than JOIN especially for someone who's learning SQL. This query joins the two tables according to product_id and returns those products that are active. I'm assuming "active" is boolean type.

SELECT t1.*
FROM Table1 t1, Table2 t2
WHERE t1.product_id = t2.product_id AND t2.active = TRUE

W3Schools has a good basic level tutorial of different kinds of JOINs. See INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

Upvotes: 6

Fosco
Fosco

Reputation: 38526

It's pretty simple to join two tables:

select t1.* 
from Table1 t1
join Table2 t2 on t1.product_id = t2.product_id
where t2.active = 'Y'

Upvotes: 4

Related Questions