Get only rows where exists in table?

Question

I need to select all rows from table: Announcements where there are exist rows in table Categories_announcements for user by condition:

Categories_user.category_id = Categories_announcements.category_id

I tried SQL query, see link

It should returns me onlu one row Announcements.id = 1, because user has only one category in Categories_user.category_id.

EDIT:

I have tested SQL that you shared, so this is query:

select *
from `announcements`
where exists (
        select 1
        from `announcement_category`
        inner join `user_category` on `user_category`.`category_id` = `announcement_category`.`category_id`
        where `Auser_category`.`user_id` = 1
            and `announcement_category`.`announcement_id` = announcements.id
        )

It returns me an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') LIMIT 0, 25' at line 7

Using SQLFiddle is fine. But you should also add the query to you question. — Paul Spiegel
– Paul Spiegel, Commented Feb 25, 2017 at 15:56

Paul Spiegel · Accepted Answer · 2017-02-25 16:07:40Z

2

I don't think you need an EXISTS subquery. A basic join statement should work.

select a.* 
from Categories_user cu
join Categories_announcements ca on ca.category_id = cu.category_id
join Announcements a on a.id = ca.announcement_id
where cu.user_id = 1

answered Feb 25, 2017 at 16:07

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darama Over a year ago

Can you help me write this query in Laravel eloquent?

Paul Spiegel Over a year ago

@Darama If you want an eloquent solution you would need to define the models and relations first. But i wouldn't change this question too much, because you've already got a lot of answers. Write a new question tagged with laravel. After defining the relations the answer might be as simple as $user->announcements;.

Gurwinder Singh · Accepted Answer · 2017-02-25 15:56:21Z

1

You need to correlate the subquery with the Announcements table:

select *
from Announcements a
where exists (
        select 1
        from Categories_announcements ca
        inner join Categories_user cu on cu.category_id = ca.category_id
        where cu.user_id = 1
            and ca.announcement_id = a.id
        )

Demo

answered Feb 25, 2017 at 15:56

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-02-25 15:57:11Z

1

You are close. Try this:

select a.*
from Announcements a
where exists (select 1
              from Categories_announcements ca join
                   Categories_user cu
                   on cu.category_id = ca.category_id 
              where ca.announcement_id = a.id and
                    cu.user_id = 1
             );

Notes:

The key thing you are missing is the correlation clause to the outer query (ca.announcement_id = a.id).
The left join is superfluous. The where clause turns it into an inner join.
Table aliases make the query easier to write and to read.

answered Feb 25, 2017 at 15:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Mureinik · Accepted Answer · 2017-02-25 15:57:51Z

1

You'r query is missing a relationship between the announcement table in the outer query to the condition in the inner one:

SELECT *
FROM   announcements a
WHERE  EXISTS (SELECT    * 
               FROM      categories_announcements ca
               LEFT JOIN categories_user cu ON cu.category_id = ca.category_id 
               WHERE     cu.user_id = 1 AND
                         a.id = ca.announcement_id -- Here!
              )

SQLFiddle

answered Feb 25, 2017 at 15:57

Mureinik

316k54 gold badges400 silver badges405 bronze badges

1 Comment

Darama Over a year ago

What about perfomance? is it speed request for 1000 announcements?

Dhruv Saxena · Accepted Answer · 2017-02-25 16:08:14Z

1

Another possible workaround without a sub-query:

SELECT a.id AS accouncement_id, a.name AS annoucement_name 
FROM Categories_user cu
INNER JOIN Categories_announcements ca 
        ON  cu.category_id = ca.category_id
        AND cu.user_id = 1
INNER JOIN Announcements a 
        ON ca.announcement_id = a.id;

Demo link

answered Feb 25, 2017 at 16:08

Dhruv Saxena

1,3462 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Get only rows where exists in table?

EDIT:

5 Answers 5

2 Comments

Demo

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT:

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related