• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Mysql - query where ALL search criterias has to be found in second table


I im building a php/mysql based property search.

Tabel one (t1) contains all fields with just one value (title, address etc.). Table two (t2) keeps multiple records for each t1.id.

Table 2 structure:

id    t1.id    ext.id

In the searchform there are checkboxes for all possible ext.ids and I need to only return results where ALL checked values (ext.ids) is found in t2.


Checkboxes checked:

Power (ext.id = 2)
Cabel TV (ext.id = 5)
Garage (ext.id = 6)
Garden (ext.id = 10)

Now I need to pick up the records in t1 where all these ext.ids are found in t2.

Any suggestions on how to build a mysql query for this?

Thanks in advance!
1 Solution
as I understand you are looking for a query like this
FROM Table_1 T1
	INNER JOIN Table_2 T2 ON T1.id = T2.id AND T2.ext.id = 2
	INNER JOIN Table_2 T3 ON T1.id = T3.id AND T2.id = T3.id AND T3.ext.id = 5
	INNER JOIN Table_2 T4 ON T1.id = T4.id AND T2.id = T4.id AND T4.ext.id = 6
	INNER JOIN Table_2 T5 ON T1.id = T5.id AND T2.id = T5.id AND T5.ext.id = 10

Open in new window

bgpro_noAuthor Commented:
Thanks - worked perfectly!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now