Select a row that belongs to several categories.

Question

I have 2 tables. The first table is full of records. The second table determines which categories this entry belongs to:

Table 1:

entry_id | title 1 | Entry1 2 | Entry2 3 | Entry3 

table 2

 entry_id | cat_id 1 | 233 1 | 234 1 | 678 2 | 235 2 | 453 2 | 21 3 | 234 3 | 233 

I am trying to select a record with a single request for all messages belonging to several categories. For example, I want to return records related to category identifiers, 233 and 234. I believe that this requires a subquery, although I'm not quite sure. Any help to anyone? :)

Answer 1

Try the following:

 select * from entity e where exists (select * from category c where c.entry_id=e.entry_id AND c.cat_id=233) and exists (select * from category c where c.entry_id=e.entry_id AND c.cat_id=234) 

This returns strings that belong to both 233 and 234 (as I read your question, in any case, I may have misunderstood the part that belongs to several categories).

Answer 2

Learn about SQL joins .

 SELECT * FROM tbl1 JOIN tbl2 USING (entry_id) WHERE cat_id IN (233,234); 

Take a look at sqlfiddle .

---

UPDATE

To select all entries in both categories, you can group the connection results and select only those groups that contain both categories:

 SELECT tbl1.* FROM tbl1 JOIN tbl2 USING (entry_id) WHERE cat_id IN (233,234) GROUP BY entry_id HAVING COUNT(DISTINCT cat_id) = 2 

Take a look at sqlfiddle .

Obviously, COUNT(DISTINCT cat_id) can be replaced with (much less expensive) COUNT(*) if (entry_id, cat_id) is unique in tbl2 .