How to select 1 Row in MySQL Master Table Based on 2 Conditions Existing in a Detailed Table


Problem :

I have 2 tables -- master and detail

Table A is Master and contains unique rows

ID Name
1  Abc
...
7  Def
...
19 Xyz

Table B is Detail and contains multiple rows per each master row

Ref ID  Desc
1   10  abcdef
1   15  ghijk
1   20  lmnop
...
7   05  qrstu
7   10  vwxyz
7   15  ygfdt
7   20  kjqts
...
19  05  htyge
19  15  gqwes

Query Result -- I only wish to select Master Rows that have detailed rows containing both a related detail row of "10" and "15"

So only Rows 1 and Row 7 would be selected.

Thanks for helping.

Here's the shell of my query:

$query_string = '
  SELECT a.ID, Name
  FROM table_a AS a
  LEFT JOIN table_b AS b ON b.ref = a.ID
  WHERE b.ID = "10" AND b.ID = "15"
';


Solution :

SELECT a.ID, Name
FROM table_a AS a
LEFT JOIN table_b AS b ON b.ref = a.ID
WHERE b.ID IN (10,15)
group by a.ID
having count(distinct b.id) = 2

Mysql Tutorials

Mysql Howto..

How to Solve the XAMPP 1.7.7 - PHPMyAdmin - MySQL Error #2002 in Ubuntu

MySQL: How to query multiple 'statistics' in a single query?

PHP explode data and show in table

How to get selected data from combobox and upload it to mysql database?

How to connect to MySQL using C# and display a list of entries in the console?

how to compare data using redgate data compare tool

How to setup MySQL Database Relationship

mysql how to edit my.ini on some hosting site

How to insert data in mysql database using C#

how to draw polyline google maps from mysql using Javascript

How to calculate the initial Stock with MySql

Select box: how to populate days php

how to use Fuzzy look up to find the sentence in SQL?

How to display ascending or descending for the contents shown in mysql

How to select row count multiplied by non zero columns? (mysql)

How to GRANT SELECT ON tablename to PUBLIC in MySQL

How to sum each column in MySQL table with PHP

MySQL: how to search for a number that might not be unique

How to Check if value exists in a MySQL database

How to update mysql database, with a variable?

How to enable users from another domain to search records from my domain?

How to store jquery selectable into mysql db using php

How to properly loop in a stored function on MySQL?

I need advice on how to simplify/improve an ordering model in mysql

How to define the default values for a column when creating tables in MySQL?

PHP/MySQL - How to add multiple tags

how to find the connection string details for mysql in IIS?

how to escape for mysql in asp.net?

How to include database into my app

How to rollback the effect of last executed mysql query