CODEWITHSUNDEEP
mysqlsqlselectexists
Darama
Darama

Reputation: 3370

Get only rows where exists in table?

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

Upvotes: 2

Views: 742

Answers (5)

Dhruv Saxena
Dhruv Saxena

Reputation: 1346

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

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31812

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

Upvotes: 2

Mureinik
Mureinik

Reputation: 311528

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

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

Upvotes: 1

Related Questions