SQL JOIN when at least one table contains the desired value: Can this be achieved in a single query?

Question

Given a SQL database with the following tables

• TABLE Branch

  • branch_Id INT primary key

• TABLE Department

  • dept_Id INT primary key
  • branch_Id INT

• Table Branch_Desc

  • branch_Id INT primary key
  • branch_Desc VARCHAR

• TABLE Department_Desc

  • department_Id INT primary key
  • department_Desc VARCHAR

Is it possible to return a list of values in the tables above that match the following requirements:

" List all branches departments, branch descriptions and department descriptions where at least one of the descriptions (branch or department) matches a desired value " ?

The trick here is that the query is supposed to return a ONLY a matching descriptions so if we have the following scenario:

Branch: 1, Desc: test
Branch: 2, Desc: another
Department: 1, Desc: another
Department: 1, Desc: something else

With the desired value being 'another' the query should return:

Branch: 2, Branch Desc: another, Department: 1, Dept Desc: another 

Branch Desc: 'test' and Dept Desc 'something else' should not be returned, nor should be Branch 1.

Assuming that the table structure cannot be changed is it possible to write a SQL query that would return the correct results?

so far the closest I got was:

SELECT br.branch_id, bd.branch_desc, de.dept_id, dd.dept_desc
FROM branch br

LEFT JOIN branch_desc bd 
ON bd.branch_id = br.branch_id
AND UPPER(br.branch_desc) = 'value'

JOIN department de 
ON br.branch_id = de.branch_id

LEFT JOIN department_desc dd 
ON de.dept_id = dd.dept_id
AND UPPER(dd.dept_desc) = 'value'

This returns the correct values if at least one department contains a description for 'value' however when no departments contain the desired description then no rows are returned (even if there is a branch descriptions that matches 'value')

At this point I think two separate queries are required to achieve the correct results in all four possible scenarios:

1. Both Branch and Department contain descriptions that match 'value'
2. Only Branch contains descriptions that match 'value'
3. Only Department contains descriptions that match 'value'
4. Neither Branch nor Department contain descriptions that match value

If this is possible (and I have a feeling that it must be) I would appreciate any guidance towards the right direction.

Thanks in advance!

Answer 1

You have two errors in joins. Below I fixed those and used OR in the WHERE clause.

SELECT br.branch_id, bd.branch_desc, de.dept_id, dd.dept_desc
FROM branch br
INNER JOIN branch_desc bd ON db.branch_id = br.branch_id
INNER JOIN department de ON de.branch_id = br.branch_id 
--note error in your code above and below 
INNER JOIN department_desc dd ON dd.dept_id = de.dept_id
WHERE UPPER(br.branch_desc) = 'value'
   OR UPPER(dd.dept_desc) = 'value';

Answer 2

When you have a spec that reads "match this or that" consider in SQL UNION is analoguous to logical OR:

  SELECT dept_Id, branch_Id,
         Branch_Desc AS branch_or_department_description
    FROM Department
         NATURAL JOIN
         Branch_Desc
  UNION
  SELECT dept_Id, branch_Id,
         department_Desc AS branch_or_department_description
    FROM Department
         NATURAL JOIN
         ( SELECT department_Id AS dept_id, department_Desc FROM Department_Desc ) AS d;

To apply the search condition (branch_or_department_description = '<search text>') you hve various choices e.g. create a VIEW, use a derived table, etc.