CODEWITHSUNDEEP
mysqlsql
Abijeet Patro
Abijeet Patro

Reputation: 2884

SELECT query based on multiple tables

We have three tables in a MySQL Database -

  1. Company
  2. Employee
  3. Address

Company has Employees. Employees have Address. [Note they can have multiple addresses]

Please take a look at the image below for a general idea of the structure

DB Structure

We have two conditions here -

1.Get all companies whose employee(s) have at least one address listed in the Address table.

Example Query Result should contain the following companies -

Microsoft, Google, IBM

2.Get all companies whose employees have no address listed in the Address table.

Example Query Result should contain the following companies -

Yahoo

We have currently written this query which seems to be working for this particular condition- 

SELECT
  company_id,
  companies.company_name,
FROM companies
  LEFT OUTER JOIN employees    ON employees.company_id = companies.company_id
  LEFT OUTER JOIN addresses    ON address.employee_id = employees.employee_id AND address_id IS NOT NULL
WHERE address_id IS  NULL GROUP BY companies.company_id;

Is there a way to fetch these result using a single query to the database without the use of Stored Procedures? It should add a column to the result set (0 or 1) depending on whether a companies employee has an address listed.

Upvotes: 1

Views: 753

Answers (3)

BaBL86
BaBL86

Reputation: 2622

1.Get all companies whose employee(s) have at least one address listed in the Address table.

2.Get all companies whose employees have no address listed in the Address table.

Is there a way to fetch these result using a single query to the database without the use of Stored Procedures?

Try this:

SELECT * FROM companies

Updated answer:

Select c.[company_id],c.[company_name], CASE WHEN count(a.address_id)>0 THEN 1 ELSE 0 END as [flag] from Company c
left join Employee e on e.[company_id] = c.[company_id]
left join Address a on a.[employee_id] = e.[employee_id]
group by c.[ID],c.[company_name]

give me result:

ID  NAME    FLAG
2   Google  1
3   IBM 1
1   Microsoft   1
4   Yahoo   0

sqlfiddle: http://sqlfiddle.com/#!6/4163a/3

update: sorry, sqlfiddle for MSSQL. This is fo mysql: http://sqlfiddle.com/#!2/18d09/1

Upvotes: 2

DRapp
DRapp

Reputation: 48139

I would just add another column to your existing query and remove your test for IS NULL on the address. You would get all companies, and a column (flag) indicating if it has no addresses on file.

SELECT
      company_id,
      companies.company_name,
      MAX( CASE WHEN address.address_id IS NULL then 1 else 0 end ) as NoAddressOnFile
   FROM 
      companies
         LEFT OUTER JOIN employees
            ON companies.company_id = employees.company_id
            LEFT OUTER JOIN addresses    
               ON employees.employee_id = address.employee_id
   GROUP BY
      companies.company_id;

Upvotes: 2

Kacer
Kacer

Reputation: 679

You can use 'UNION' to join two or more queries into single query.

Btw. from your conditions it appears, that you actually want all companies.

Upvotes: 0

Related Questions