Get Record only if its Join record exists in other table

Question

I have two tables for Groups and Category i wanted to return the groups and categories excluding those groups which doesn't have any category here is the SQL i wrote:

SELECT 
    cat_group.subject as group_name , spcat . *
FROM
    special_event_groups AS cat_group
        LEFT JOIN
    special_event_categories AS spcat ON cat_group.id = spcat.group_id
        AND cat_group.partner_id = spcat.partner_id;

Its returning me the records of group with NULL values which doesn't have any category. Do i need to use a subquery ?

yes change left join to join or inner join (they are the same though) — Rat-a-tat-a-tat Ratatouille, Feb 26 '14 at 08:14
@piotrekkr Great that helps mates thanks a bunch!! Can any one of you add this as a answer so that i can mark it ? — Seeker, Feb 26 '14 at 08:26
@Rat-a-tat-a-tatRatatouille Sorry i can add only one user in the comment your answer was correct as well thanks alot :) — Seeker, Feb 26 '14 at 08:27
If you are new to `JOIN` syntax I found things like [Venn Diagram representations](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) of joins quite helpful in gaining a basic understanding. [Martin Smith's answer](http://stackoverflow.com/a/14011879/685760) and [Cade Roux's answer](http://stackoverflow.com/a/14003714/685760) are both great in explaining the limitations/shortcomings of Venn diagrams and provide interesting responses and alternatives. These are pretty decent resources for learning more about SQL Joins. — Mr Moose, Feb 26 '14 at 08:27
You can use OUTER LEFT JOIN too, LEFT JOIN, JOIN, it depends on the version of your database — STP38, Feb 26 '14 at 08:30

piotrekkr · Accepted Answer · 2014-02-26T09:12:56.567

What you need to do is to change LEFT JOIN to JOIN:

SELECT 
    cat_group.subject as group_name , spcat . *
FROM
    special_event_groups AS cat_group
JOIN
    special_event_categories AS spcat ON cat_group.id = spcat.group_id
    AND cat_group.partner_id = spcat.partner_id;

If joining condition is not met, LEFT JOIN show row from special_event_groups and attach NULL values in selected columns from special_event_categories. JOIN, on the other hand, never returns row when JOIN condition is not met. You can read @MrMoose comment for more info.

score 1 · Answer 2 · answered Feb 26 '14 at 08:32

You can use OUTER LEFT JOIN too, LEFT JOIN, JOIN, it depends on the version of your database.

Try this:

SELECT 
    cat_group.subject as group_name , spcat . *
FROM
    special_event_groups AS cat_group
       // LEFT JOIN, OUTER LEFT JOIN, JOIN
    special_event_categories AS spcat ON cat_group.id = spcat.group_id
WHERE cat_group.partner_id IS NULL;

Let me know if it worked

Get Record only if its Join record exists in other table

2 Answers2