CODEWITHSUNDEEP
mysqlleft-joinwhere-clause
Ayxan Əmiraslanlı
Ayxan Əmiraslanlı

Reputation: 433

mysql left join and search

I have the following data set: http://sqlfiddle.com/#!9/6f4751/3

Simply I need to get court which law_court_mesul_shexs.cif is 008003 and 516561

SELECT 
    distinct `law_court_mesul_shexs`.`court_id`, 
    `law_court`.* 
FROM `law_court` 
LEFT JOIN `law_court_mesul_shexs` 
    ON `law_court`.`court_id` = `law_court_mesul_shexs`.`court_id` 
WHERE `law_court_mesul_shexs`.`cif` = '008003' 
    OR `law_court_mesul_shexs`.`cif`= '516561'

This sql works but it also returns court id 114 because 114 also have a CIF 008003.

But I want to get only court id 113, because I need to get courts which only CIF is 008003 and 516561.

Upvotes: 1

Views: 33

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You could use following approaches to get your desired result set 

SELECT `c`.* 
FROM `law_court` c
JOIN (SELECT court_id
     FROM law_court_mesul_shexs
     WHERE cif IN('008003' , '516561')
     GROUP BY court_id
     HAVING COUNT(DISTINCT mesul_shexs_id) = 2
     ) s
ON `c`.`court_id` = `s`.`court_id`;

Or

SELECT `c`.* 
FROM `law_court` c
JOIN (SELECT court_id
     FROM law_court_mesul_shexs
     WHERE cif IN('008003' , '516561')
     GROUP BY court_id
     HAVING SUM(cif ='008003') > 0
     AND SUM(cif ='516561') > 0
     ) s
ON `c`.`court_id` = `s`.`court_id`

Or using exists

SELECT `c`.* 
FROM `law_court` c
WHERE EXISTS (
      SELECT 1
      FROM  law_court_mesul_shexs
      WHERE cif = '008003' AND court_id = c.court_id
) AND (
      SELECT 1
      FROM  law_court_mesul_shexs
      WHERE cif = '516561' AND court_id = c.court_id
)

Demo

Upvotes: 1

Related Questions