CODEWITHSUNDEEP
mysql
VigoKrumins
VigoKrumins

Reputation: 21

MySQL select data from 3 tables

I've 3 MySQL tables:

** cities **
------------------
id  name
------------------
1   New York
2   Los Angeles
3   San Francisco
...

** companies **
------------------
id  name
------------------
1   Company 1 Ltd.
2   Company 2 Ltd.
3   Company 3 Ltd.
...

** city_companies **
-------------------------
id  city_id  company_id
-------------------------
1   2        3
2   1        2
3   3        3
4   3        2
5   1        1

For example with this query:

SELECT a.*, c.*
FROM cities a
INNER JOIN city_companies b ON a.id = b.city_id
INNER JOIN companies c ON b.company_id = c.id
WHERE a.city_id = '1'

This query will return the list of companies that belongs to 'New York' (specified in 'city_companies' table). I need to get opposite result - list of companies that doesn't belong to 'New York'.

Upvotes: 0

Views: 1646

Answers (4)

gms
gms

Reputation: 335

SELECT companies.name from companies
WHERE companies.name NOT IN
(SELECT companies.name FROM companies 
INNER JOIN city_companies ON companies.id = city_companies.company_id
INNER JOIN cities ON city_companies.city_id = cities.id
WHERE cities.name = 'New York');

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS cities;

CREATE TABLE cities 
(id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO cities VALUES
(1,'New York'),
(2,'Los Angeles'),
(3,'San Francisco');

DROP TABLE IF EXISTS companies;

CREATE TABLE companies 
(id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO companies VALUES
(1,'Company 1 Ltd.'),
(2,'Company 2 Ltd.'),
(3,'Company 3 Ltd.');

DROP TABLE IF EXISTS city_companies;

CREATE TABLE city_companies
(city_id INT NOT NULL
,company_id INT NOT NULL
,PRIMARY KEY(city_id,company_id)
);

INSERT INTO city_companies VALUES
(2,3),
(1,2),
(3,3),
(3,2),
(1,1);


SELECT DISTINCT x.*
  FROM companies x 
  JOIN city_companies xy1 
    ON xy1.company_id = x.id 
  JOIN cities y 
    ON y.id = xy1.city_id 
  LEFT 
  JOIN city_companies xy2 
    ON xy2.company_id = xy1.company_id 
   AND xy2.city_id <> xy1.city_id 
  LEFT 
  JOIN cities y2 
    ON y2.id = xy2.city_id 
   AND y2.name = 'new york' 
 WHERE y.name <> 'new york' 
   AND y2.id IS NULL;
id  name
 3  Company 3 Ltd.

http://rextester.com/CRULQ65167

Upvotes: 1

Chintan Udeshi
Chintan Udeshi

Reputation: 352

Below query will give you expected output

select * from companies c where not exists ( 
select 1 from cities inner join city_companies on cities.id = city_companies.city_id inner join companies on companies.id = city_companies.company_id where cities.name 
= 'new york' and companies.id = c.id)

Upvotes: 1

Bogdan
Bogdan

Reputation: 397

Try this:

select *
from cities
left join city_companies on cities.id =  city_companies.city_id
left join companies on companies.id = city_companies.company_id
where 
cities.name != 'New York';

Upvotes: 0

Related Questions