CODEWITHSUNDEEP
mysqlsql
Chud37
Chud37

Reputation: 5007

Selecting all with related products

I have a table called teachings:

id,name,description

And a products table: id,name,description,teachingID

I want to select all from teachings that have products associated to them. If there is a teaching that has no products in the products table, I don't want it.

So far I came up with:

SELECT * FROM `teachings`
LEFT JOIN `products` ON `products`.`teachingID` = `teachings`.`id`
WHERE COUNT(`products`) > 0

But that doesn't work :( Can anyone point me in the right direction?

Upvotes: 1

Views: 64

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Here is an additional answer simply to show the IN clause I was talking of in a comment. The task is very simple: Get teachings records for which exist a product record. In other words: Get teachings records that are mentioned in the products table. So one uses EXISTS or IN here.

To join the tables only obfuscates what is being done, so don't do that.

The EXISTS query:

select *
from teachings t
where exists (select * from products p where p.teachingid = t.id);

The IN query, which is even more readable, because the subquery is not correlated to the main query:

select *
from teachings t
where id in (select teachingid from products p);

Upvotes: 1

gzaxx
gzaxx

Reputation: 17590

If you want only to return teachings with at least one product associated with it then use exists subquery. This way you do not have to count products per teaching.

My mysql got rusty but this should do

select *
from teachings t
where exists
(
   select 1
   from products p
   where p.teachingID = t.id
)

Upvotes: 3

Related Questions