MySQL select data from 3 tables

Question

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'.

New York' should return 'Company 3 Ltd.' because it's not added in 'city_companies' table. What about Company 1 Ltd. thats in New Your as well??? — RiggsFolly
– RiggsFolly, Commented Feb 13, 2017 at 10:42
Your question is not clear. Read it back and try and edit it so you are sure we understand what you want — RiggsFolly
– RiggsFolly, Commented Feb 13, 2017 at 10:42
Off the top of my head I don't see any query that would take NY as input and give you Company 3 as output, but what you can do is do a LEFT JOIN and thus see where the missing values are - I ended up figuring something out, see my answer, let me know if it works for you. — Bogdan
– Bogdan, Commented Feb 13, 2017 at 10:42

Chintan Udeshi · Accepted Answer · 2017-02-13 11:58:11Z

1

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)

answered Feb 13, 2017 at 11:58

Chintan Udeshi

3521 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Strawberry · Accepted Answer · 2017-02-13 12:31:27Z

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

Bogdan · Accepted Answer · 2017-02-13 11:17:19Z

0

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';

edited Feb 13, 2017 at 11:17

answered Feb 13, 2017 at 10:46

Bogdan

3972 silver badges7 bronze badges

10 Comments

Strawberry Over a year ago

Except for the special case of IS NULL, LEFT JOIN x... WHERE x is the same as INNER JOIN x...

Bogdan Over a year ago

if you want the reverse of your table "city_companies", then you just need to adjust the previous query: where cities.name is NOT NULL and companies.name is NULL;

Bogdan Over a year ago

@Strawberry yes, except one is LEFT JOIN the other is INNER JOIN. Or am I missing something ?

Strawberry Over a year ago

Yes. You're missing something.

Bogdan Over a year ago

I see what you mean. Except that in our case, we want to look for the "special case" is null. So we need a left join and not an inner one.

|

gms · Accepted Answer · 2017-02-13 14:59:03Z

0

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');

answered Feb 13, 2017 at 14:59

gms

3353 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL select data from 3 tables

4 Answers 4

Comments

Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related