title: Select rows from a table based on another row in the same table
body: <p>I have a single table containing details about multiple residential properties.
  There are only 4 Columns in the table named as follows.</p>
  <blockquote>
  <p><strong>ID</strong> = Unique Identifier</p>
  <p><strong>tmplvarid</strong> = kind of like a record type code. i.e. Address, Price,
  Status, Image etc.... (there are probably 20 different record types)</p>
  <p><strong>Contentid</strong> = used to group records belonging to the same property.</p>
  <p><strong>Value</strong> = Actual details for the record.</p>
  </blockquote>
  <p>What I need to do is select some fields for records of properties that have a status of &quot;sold&quot; for example.  It would make sense to me if I could just join two tables with related information but the information is all in one table.</p>
  <p>I can see this data structure would make it easy to add a new field to the application with out a need to change the tables but I'm stuck on how to query the table to return related information.</p>
  <p>Any pointers in the right direction?</p>
  <p>Thanks
  David.</p>
  <p>EDIT
  Here is an example of all the records relating to one property. Obviously there are many properties with different property id's.  (Example Property ID 183)</p>
  <p>As and example, I want to select  the Street number (varid=34), street name (varid=39) and the Image name (varid=36) for ALL properties have been &quot;Sold&quot; (varid= 106)</p>
  <p>I hope my question make more sense now.</p>
  <p>PS I did try to research the problem but my attempted SQL wasn't worth posting.
  I thought about nested Select statements or linking the table to itself.  I also looked for someone else who had solved a similar problem but didn't find anyone.</p>
  <p>Expected Output:</p>
  <pre><code>3758    34  183 2/150
  3759    39  183 SMITH STREET
  3779    36  183 [{&quot;MIGX_id&quot;:1,&quot;ImageID&quot;:&quot;1&quot;,&quot;FileName&quot;:&quot;00686_01.jpg&quot;,&quot;ImageSrc&quot;:&quot;assets\/images\/properties\/00686_01.jpg&quot;,&quot;AltImage&quot;:&quot;00686_01.jpg&quot;,&quot;TimeModified&quot;:&quot;26 Jul 2013 11:12:52&quot;,&quot;Format&quot;:&quot;Photograph&quot;},{&quot;MIGX_id&quot;:2,&quot;ImageID&quot;:&quot;2&quot;,&quot;FileName&quot;:&quot;00686_02.jpg&quot;,&quot;ImageSrc&quot;:&quot;assets\/images\/properties\/00686_02.jpg&quot;,&quot;AltImage&quot;:&quot;00686_02.jpg&quot;,&quot;TimeModified&quot;:&quot;26 Jul 2013 11:12:52&quot;,&quot;Format&quot;:&quot;Photograph&quot;},{&quot;MIGX_id&quot;:3,&quot;ImageID&quot;:&quot;3&quot;,&quot;FileName&quot;:&quot;00686_03.jpg&quot;,&quot;ImageSrc&quot;:&quot;assets\/images\/properties\/00686_03.jpg&quot;,&quot;AltImage&quot;:&quot;00686_03.jpg&quot;,&quot;TimeModified&quot;:&quot;26 Jul 2013 11:12:52&quot;,&quot;Format&quot;:&quot;Photograph&quot;
  </code></pre>
  <p>=====================================================================</p>
  <pre><code>id      varid   contid  value
  3756    32  183 686
  3757    33  183 Rent
  3758    34  183 2/150
  3759    39  183 SMITH STREET
  3760    40  183 FAIR FEILD
  3761    41  183 2165
  3762    42  183 NSW
  3763    51  183 Residential
  3764    52  183 Unit
  3765    61  183 300
  3766    62  183 W1
  3767    63  183 2015-1-6 ::
  3768    66  183 Yes
  3769    110 183 18
  3770    106 183 Sold
  3771    107 183 2015-1-20 10:27:25
  3772    35  183 2
  3773    76  183 1
  3774    79  183 1
  3775    95  183 1
  3776    96  183 Communal
  3777    104 183 1
  3778    105 183 1
  3779    36  183 [{&quot;MIGX_id&quot;:1,&quot;ImageID&quot;:&quot;1&quot;,&quot;FileName&quot;:&quot;00686_01.jpg&quot;,&quot;ImageSrc&quot;:&quot;assets\/images\/properties\/00686_01.jpg&quot;,&quot;AltImage&quot;:&quot;00686_01.jpg&quot;,&quot;TimeModified&quot;:&quot;26 Jul 2013 11:12:52&quot;,&quot;Format&quot;:&quot;Photograph&quot;},{&quot;MIGX_id&quot;:2,&quot;ImageID&quot;:&quot;2&quot;,&quot;FileName&quot;:&quot;00686_02.jpg&quot;,&quot;ImageSrc&quot;:&quot;assets\/images\/properties\/00686_02.jpg&quot;,&quot;AltImage&quot;:&quot;00686_02.jpg&quot;,&quot;TimeModified&quot;:&quot;26 Jul 2013 11:12:52&quot;,&quot;Format&quot;:&quot;Photograph&quot;},{&quot;MIGX_id&quot;:3,&quot;ImageID&quot;:&quot;3&quot;,&quot;FileName&quot;:&quot;00686_03.jpg&quot;,&quot;ImageSrc&quot;:&quot;assets\/images\/properties\/00686_03.jpg&quot;,&quot;AltImage&quot;:&quot;00686_03.jpg&quot;,&quot;TimeModified&quot;:&quot;26 Jul 2013 11:12:52&quot;,&quot;Format&quot;:&quot;Photograph&quot;}]
  3780    128 183 assets/images/properties/00686_01.jpg
  3781    119 183 [{&quot;MIGX_id&quot;:1,&quot;InspectionID&quot;:&quot;1&quot;,&quot;InspectionDate&quot;:&quot;Sat, 24 Jan 2015&quot;,&quot;InspectionStart&quot;:&quot;15:30:00&quot;,&quot;InspectionEnd&quot;:&quot;15:45:00&quot;}]
  3782    120 183 18
  3783    121 183 Bob Jobbins
  3784    122 183 9735 9999
  3785    124 183 0406619999
  3786    125 183 email@domain.com
  3787    126 183 Active
  3788    127 183 20 Jan 2015 10:27:25
  </code></pre>