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


i have single table containing details multiple residential properties. there 4 columns in table named follows.

id = unique identifier

tmplvarid = kind of record type code. i.e. address, price, status, image etc.... (there 20 different record types)

contentid = used group records belonging same property.

value = actual details record.

what need select fields records of properties have status of "sold" example. make sense me if join 2 tables related information information in 1 table.

i can see data structure make easy add new field application out need change tables i'm stuck on how query table return related information.

any pointers in right direction?

thanks david.

edit here example of records relating 1 property. there many properties different property id's. (example property id 183)

as , example, want select street number (varid=34), street name (varid=39) , image name (varid=36) properties have been "sold" (varid= 106)

i hope question make more sense now.

ps did try research problem attempted sql wasn't worth posting. thought nested select statements or linking table itself. looked else had solved similar problem 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@domain.com 3787    126 183 active 3788    127 183 20 jan 2015 10:27:25

no problem having info in 1 table: can join table itself. should work:

select   sold.contentid,streetno.value streetno,streetname.value street,imagename.value imagename  properties sold join properties streetno on streetno.tempvarid=34 , streetno.contentid=sold.contentid join properties streetname on streetname.tempvarid=39 , streetname.contentid=sold.contentid join properties imagename on imagename.tempvarid=36 , imagename.contentid=sold.contentid

there may syntax or similar problem above. did not have time acutally test because need go concrete, confident approach solve problem.


Comments

Post a Comment

Popular posts from this blog

angularjs - ADAL JS Angular- WebAPI add a new role claim to the token -

i have created angular spa webapi backend using adal js authentication.as there no roles in ad, need manually add role claims in order give users access different api controllers. the roles stored in database. expecting inject claim through call webapi after authentication ad. webapi code might this. identity.addclaim(new claim("role", "user")); var ticket = new authenticationticket(identity, props); var accesstoken = startup.oauthbeareroptions.accesstokenformat.protect(ticket); is possible replace adal idtoken new token? is viable solution or there other better way handle this? as initial token generated azuread, possible edit token add new claim? appreciated. graph api supporting group claims. see here: http://justazure.com/azure-active-directory-part-4-group-claims/ if @ examples on page, users assigned groups , app can check group in claims. in current version of portal, need app manifest , modify it.
Read more

php - CakePHP HttpSockets send array of paramms -

i problem when make request (i can request). $link = 'http://uri/path/?param1=1&sid=2&sid=3&sid=4' $http = $httpsocket = new httpsocket(); $res = $httpsocket->get($link); if check request pr($http); i see : [line] => /url/path/?param1=1&sid%5b0%5d=2&sid%5b1%5d=3&sid%5b2%5d=4 http/1.1 and i'm getting empty response because server doesn't know parameters sid%5b0%5d=2 when try send parameters array : $data = array('param1' => '1', 'sid' => array('2','3', '4')); i see same changes %5b0%5d - additional indexes. how fix it? unfortunately , can not change server side, if send request in browser, i'll normal response in json format. when make request, can parameters this: $param1 = $_get['param1']; $sid= $_get['sid']; tip: use diferente names parameters. have multiples sid. use sid1, sid2, sid3... update: can parameters this:
Read more

node.js - Using Node without global install -

i'm working on few node & electron (atom shell) projects , i'm curious how them work without user having install node globally. i've seen few applications including node directory in project contains node.exe binary. how go setting node project use binary? edit: thought should add, i've searched everywhere answer this, unrelated results.
Read more