title: Select query to retrieve results based on multiple values for a one column (AND condition)
body: <p>There are three tables as follows</p>
  
  <h2>Table 1 - Vehicle</h2>
  
  <p>Vehicle_Id PRIMARY KEY <br/>
  Name <br/>
  Serial_Number   </p>
  
  <h2>Table 2 - Propertyname</h2>
  
  <p>Property_Id PRIMARY KEY <br/>
  Name</p>
  
  <h2>Table 3 - Property</h2>
  
  <p>Property_Id (FOREIGN KEY) references Property_Id from Propertyname table <br/>
  Value <br/>
  Id (FOREIGN KEY) references Vehicle_Id from Vehicle table</p>
  
  <h2>Sample Data</h2>
  
  <h2>Table 1 - Vehicle</h2>
  
  <pre>Vehicle_Id    Name    Serial_Number </pre>
  
  <pre>1000        WagonR    Maruti-WagonR <br/> </pre>
  
  <pre>1001        Estilo    Maruti-Estilo <br/> </pre>
  
  <pre>1002        Zen       Maruti-Zen    <br/> </pre>
  
  <h2>Table 2 - Propertyname</h2>
  
  <pre>Property_Id    name</pre>
  
  <pre>100            Mileage <br/> </pre>
  
  <pre>101            FuelType<br/> </pre>
  
  <pre>102            Country <br/> </pre>
  
  <h2>Table 3 - Property</h2>
  
  <pre>Property_Id    Value    Id </pre>
  
  <pre>100            50       1000 <br/> </pre>
  
  <pre>101            Petrol   1000 <br/> </pre>
  
  <pre>102            India    1000 <br/> </pre>
  
  <pre>100            35       1001 <br/> </pre>
  
  <pre>101            Diesel   1002 <br/> </pre>
  
  <p>From the three tables we can see that each vehicle has a set of properties which are kept as rows in the Property table. This is done so that in case we add more properties to a vehicle we add a row entry for the vehicle rather than adding a column which is not feasible.</p>
  
  <p>Now I have a UI where in i can search for vehicles based on their</p>
  
  <p>1) Name, Serial_Number - These are available as columns in the Vehicle table &amp; <br/> 
  2) Mileage, FuelType, Country - These are the properties for the particular Vehicle </p>
  
  <p>The user can enter any combination of values in 1) and 2) above to search through the list of vehicles. </p>
  
  <p>Eg:- If the user enters Mileage as 50 and FuelType as Diesel, i should fetch results of vehicles which have Mileage as 50 AND FuelType as Diesel. </p>
  
  <p>I tried the following SQL statement to do the same </p>
  
  <pre class="lang-sql prettyprint-override"><code>select vh.Vehicle_Id, vh.name, pt.Property_Id, pn.name, pt.value
  from property pt
  inner join propertyname pn
  on pt.Property_Id=pn.Property_Id
  inner join vehicle vh
  on vh.Vehicle_Id=pt.Id
  and ((pn.Name='Mileage' AND pt.Value='50') AND (pn.Name='FuelType' AND pt.Value='Petrol'));
  </code></pre>
  
  <p>But i get zero results due to the AND condition on the single row result. But if i change the last AND condition to OR i get two results which match the criteria. </p>
  
  <p>But I am trying to fetch Vehicles which matches both the properties matching. </p>
  
  <p>I have tried using INTERSECT, inner join of two SQL statements etc. But couldnt get the result. </p>
  
  <p>Could anyone suggest me the best way to search on two property name,value pairs which exist as rows and not columns to get the exact search results. </p>