CODEWITHSUNDEEP
sqljoin
Ben Saufley
Ben Saufley

Reputation: 3366

Left Join SQL based on criteria that may be missing

I've got several tables that I want to merge for one big query. The criteria for the search are based on two tables: section_types and sections. I want to pull all section_types and their associated sections where the sections match certain criteria, PLUS any section_types that are active but don't have any sections associated. It seems like a basic LEFT JOIN, but I think because some of my filter criteria are based on sections, I can't get section_types that have no associated sections

`section_types`

id | name | active
---+------+-------
 1 | a    | 1
 2 | b    | 0
 3 | c    | 1

`sections`

type | issue | location 
-----+-------+----------
1    | 0611  | 1
2    | 0611  | 1
1    | 0511  | 1

Say I want to pull all sections for issue 0611 at location 1, plus any empty section types. Like so:

(edited. see below)

But I'm only getting section_types that have corresponding sections. So in this query, section_types row 3 would not show up. What am I doing wrong?

EDIT:

I'm getting all the section_types now, but not all the sections I need. I guess LEFT JOIN will do that. There can be many sections for each section_type, or none. My query is at this point now:

SELECT * FROM `section_types` st
    RIGHT JOIN `sections` s
        ON s.type=st.id
            AND s.issue='0611'
            AND s.location=1
WHERE st.active OR s.issue IS NOT NULL
ORDER BY st.id

which gets me:

id | name | active | issue | location
---+------+--------+-------+---------
1  | a    | 1      | 0611  | 1
2  | b    | 0      | 0611  | 1
3  | c    | 1      |       |

but I still need that second type-1 section

Upvotes: 0

Views: 382

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Is this what you need?

All section_types and ALL their related sections where at least one section has issue '0611' and location 1. Plus all the rest section_types that are active:

    SELECT *
    FROM section_types st
        JOIN sections s
            ON s.type = st.id
    WHERE EXISTS
        ( SELECT *
            FROM sections s2
            WHERE s2.type = st.id
              AND s2.issue = '0611'
              AND s2.location = 1
        ) 
UNION ALL
    SELECT *, NULL, NULL, NULL
    FROM section_types st
    WHERE st.active
      AND NOT EXISTS
        ( SELECT *
            FROM sections s2
            WHERE s2.type = st.id
              AND s2.issue = '0611'
              AND s2.location = 1
        ) 
ORDER BY id

Upvotes: 0

Brandon J. Boone
Brandon J. Boone

Reputation: 16472

EDIT

I deleted this, but based on the conversation, I think it accomplishes what you're looking for.

ORIGINAL

Feels like a hack... but I think it works.

Declare @tmp TABLE(
    id int,
    name varchar(50),
    active int,
    type int,
    issue int,
    location int
)
Insert Into @tmp
SELECT * FROM section_types st
    LEFT JOIN sections s
        ON st.id=s.type
            AND s.issue='0611'
            AND s.location=1 
WHERE st.active = 1 OR s.issue IS NOT NULL
ORDER BY st.id

Select * FROM @tmp 
UNION 
Select 
    *, NULL, NULL, NULL 
From 
    section_types 
WHERE 
    id NOT IN ( SELECT id FROM @tmp)
    AND active = 0

Upvotes: 1

Tobu
Tobu

Reputation: 25436

You just have your tables reversed. LEFT OUTER JOIN requires the left table to have a row for the ON condition. Use a RIGHT OUTER JOIN or swap the tables.

Upvotes: 0

Related Questions