CODEWITHSUNDEEP
mysqlsql
ferigalung
ferigalung

Reputation: 3

How to SELECT data which have relation with 2 or more data in MANY to MANY tables

I have 3 tables in mySQL called tb_tour_trip, tb_facilities and tb_master_facilities which have many to many relationship. I want to select 1 data in tb_tour_trip which have 2 or more master_facilities. example : i wanna select trip which have facilities: AC and WiFi

This is the tb_tour_trip table :

tb_tour_trip
============
id || name  || description  || price
===================================
1  || trip1 || example trip || $200
2  || trip2 || example trip || $300

This is the tb_facilities table (For MANY to MANY relations):

tb_facilities
============
id || id_master_facilities_ref || id_tour_trip_ref
===================================
1  || 1                        || 1 
2  || 2                        || 1 
3  || 1                        || 2
3  || 3                        || 2

And this is my tb_master_facilities table:

tb_master_facilities
============
id || name  || status
====================
1  || WiFi  || 1
2  || AC    || 1
3  || TV    || 1

if tried this Query :

SELECT id_tour_trip_ref FROM tb_facilities WHERE id_master_facilities_ref IN(1,2);

this show me id_tour_trip which have facilities : AC or Wifi, but that's not what i want.

I want the id_tour_trip which have AC AND Wifi only.

Exactly output is just :

id_tour_trip_ref
================
1

How can i do that? thanks before.

Upvotes: 0

Views: 87

Answers (5)

ferigalung
ferigalung

Reputation: 3

i've found the answer from forum on Facebook. thanks for all your advice :)

This is the exactly i need.

SELECT id_tour_trip_ref, GROUP_CONCAT(id_master_facilities_ref) AS facilities FROM tb_facilities GROUP BY id_tour_trip_ref HAVING FIND_IN_SET('1', facilities) > 0 AND FIND_IN_SET('2', facilities) > 0

Upvotes: 0

Armin
Armin

Reputation: 157

I would join all of the tables together and just do 'where condition1 and condition2. This is propably less efficient but imho more comprehensive.

select distinct tb_tour_trip.id from tb_tour_trip 

inner join tb_facilities on tb_facilities.id_tour_trip_ref = tb_tour_trip.id
inner join tb_master_facilities on tb_master_facilities.id = tb_facilities.id_master_facilities_ref 

where tb_master_facilities.name = 'WiFi'
and tb_master_facilities.name = 'AC'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

One method is aggregation with a HAVING clause:

SELECT f.id_tour_trip_ref 
FROM tb_facilities f INNER JOIN
     tb_master_facilities
     ON f.id = mf.id 
WHERE f.name IN ('WiFi', 'AC') 
GROUP BY f.id_tour_trip_ref
HAVING COUNT(*) = 2;

Upvotes: 0

FanoFN
FanoFN

Reputation: 7124

If only to get your exact output, this could also work:

SELECT id_tour_trip_ref FROM tb_facilities INNER JOIN tb_master_facilities ON tb_facilities.id=tb_master_facilities.id WHERE name IN ("WiFi","AC") AND id_master_facilities_ref IN (1,2) GROUP BY id_tour_trip_ref;

Upvotes: 0

e_i_pi
e_i_pi

Reputation: 4820

There's a number of ways to do this, but a reasonably easy to read/maintain method is this:

SELECT id
FROM tb_tour_trip AS ttt
WHERE (
    SELECT COUNT(DISTINCT id_master_facilities_ref)
    FROM tb_facilities AS tbf
    WHERE tbf.id_tour_trip_ref = ttt.id
    AND tbf.id_master_facilities_ref IN (1, 2)
) = 2;

Upvotes: 2

Related Questions