CODEWITHSUNDEEP
mysql
David P
David P

Reputation: 411

Select rows from a table based on another row in the same table

I have a single table containing details about multiple residential properties. There are only 4 Columns in the table named as follows.

ID = Unique Identifier

tmplvarid = kind of like a record type code. i.e. Address, Price, Status, Image etc.... (there are probably 20 different record types)

Contentid = used to group records belonging to the same property.

Value = Actual details for the record.

What I need to do is select some fields for records of properties that have a status of "sold" 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.

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.

Any pointers in the right direction?

Thanks David.

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)

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 "Sold" (varid= 106)

I hope my question make more sense now.

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.

Expected Output:

3758    34  183 2/150
3759    39  183 SMITH STREET
3779    36  183 [{"MIGX_id":1,"ImageID":"1","FileName":"00686_01.jpg","ImageSrc":"assets\/images\/properties\/00686_01.jpg","AltImage":"00686_01.jpg","TimeModified":"26 Jul 2013 11:12:52","Format":"Photograph"},{"MIGX_id":2,"ImageID":"2","FileName":"00686_02.jpg","ImageSrc":"assets\/images\/properties\/00686_02.jpg","AltImage":"00686_02.jpg","TimeModified":"26 Jul 2013 11:12:52","Format":"Photograph"},{"MIGX_id":3,"ImageID":"3","FileName":"00686_03.jpg","ImageSrc":"assets\/images\/properties\/00686_03.jpg","AltImage":"00686_03.jpg","TimeModified":"26 Jul 2013 11:12:52","Format":"Photograph"

=====================================================================

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 [{"MIGX_id":1,"ImageID":"1","FileName":"00686_01.jpg","ImageSrc":"assets\/images\/properties\/00686_01.jpg","AltImage":"00686_01.jpg","TimeModified":"26 Jul 2013 11:12:52","Format":"Photograph"},{"MIGX_id":2,"ImageID":"2","FileName":"00686_02.jpg","ImageSrc":"assets\/images\/properties\/00686_02.jpg","AltImage":"00686_02.jpg","TimeModified":"26 Jul 2013 11:12:52","Format":"Photograph"},{"MIGX_id":3,"ImageID":"3","FileName":"00686_03.jpg","ImageSrc":"assets\/images\/properties\/00686_03.jpg","AltImage":"00686_03.jpg","TimeModified":"26 Jul 2013 11:12:52","Format":"Photograph"}]
3780    128 183 assets/images/properties/00686_01.jpg
3781    119 183 [{"MIGX_id":1,"InspectionID":"1","InspectionDate":"Sat, 24 Jan 2015","InspectionStart":"15:30:00","InspectionEnd":"15:45:00"}]
3782    120 183 18
3783    121 183 Bob Jobbins
3784    122 183 9735 9999
3785    124 183 0406619999
3786    125 183 [email protected]
3787    126 183 Active
3788    127 183 20 Jan 2015 10:27:25

Upvotes: 1

Views: 2610

Answers (1)

Jim Mc
Jim Mc

Reputation: 346

No problem having the info in one table: You can join a table to itself. Something like this should work:

SELECT
  sold.ContentID,streetno.Value StreetNo,streetname.Value Street,imagename.Value ImageName
 FROM properties sold
JOIN properties streetno ON streetno.tempvarid=34 AND streetno.ContentID=sold.ContentID
JOIN properties streetname ON streetname.tempvarid=39 AND streetname.ContentID=sold.ContentID
JOIN properties imagename ON imagename.tempvarid=36 AND imagename.ContentID=sold.ContentID

There may be a syntax or similar problem with the above. I did not have time to acutally test it because I need to go get some concrete, but I am confident this approach will solve your problem.

Upvotes: 1

Related Questions