Skip to content

SQL IN

Jump to bottom
PietroGarbolino edited this page Apr 9, 2020 · 1 revision

Query: SELECT TrackId, Name, AlbumId FROM Tracks WHERE AlbumId IN (SELECT AlbumId FROM Albums WHERE ArtistId = 1 );

Codice:

[{"id":"76ac7f35.d8caf","type":"tab","label":"Compito ","disabled":false,"info":""},{"id":"6d095a44.063454","type":"comment","z":"76ac7f35.d8caf","name":"Pietro Garbolino Boot","info":"","x":1320,"y":100,"wires":[]},{"id":"b6155f6e.56be9","type":"inject","z":"76ac7f35.d8caf","name":"SELECT TrackId, Name, AlbumId FROM Tracks WHERE AlbumId IN ( SELECT AlbumId FROM Albums WHERE ArtistId = 1);","topic":"1","payload":"","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":620,"y":100,"wires":[["ecf47e7f.91ae2"]]},{"id":"21b717f6.3547d8","type":"sqlite","z":"76ac7f35.d8caf","mydb":"2bb9331e.b8217c","sqlquery":"msg.topic","sql":"","name":"SQLite IN Database","x":1120,"y":480,"wires":[["36190477.fe612c","ea56612d.e1d24"]]},{"id":"4f6f27b2.4adab8","type":"ui_template","z":"76ac7f35.d8caf","group":"6ee5fcdd.731954","name":" TrackId, Name, AlbumId","order":1,"width":"6","height":"16","format":"<style>\n.table\n{\n \n background:lightblue;\n}\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n <tr>\n <th>TrackId</th> \n <th>Name</th>\n <th>AlbumId </th> \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:50\">\n <td>{{msg.payload[$index].TrackId}}</td>\n <td>{{msg.payload[$index].Name}}</td>\n <td>{{msg.payload[$index].AlbumId}}</td>\n </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":1390,"y":400,"wires":[[]]},{"id":"ecf47e7f.91ae2","type":"function","z":"76ac7f35.d8caf","name":"SELECT TrackId, Name, AlbumId FROM Tracks WHERE AlbumId IN ( SELECT AlbumId FROM Albums WHERE ArtistId = msg.payload.S1);","func":"msg.topic = \"SELECT TrackId, Name, AlbumId FROM Tracks WHERE AlbumId IN ( SELECT AlbumId FROM Albums WHERE ArtistId = \" + msg.payload.S1 + \");\"\nreturn msg;\n\n\n","outputs":1,"noerr":0,"x":670,"y":180,"wires":[["e17741ef.1bc58","21b717f6.3547d8","4f6f27b2.4adab8"]]},{"id":"e17741ef.1bc58","type":"debug","z":"76ac7f35.d8caf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1330,"y":180,"wires":[]},{"id":"36190477.fe612c","type":"change","z":"76ac7f35.d8caf","name":"","rules":[{"t":"set","p":"selected","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1370,"y":480,"wires":[["4f6f27b2.4adab8"]]},{"id":"ea56612d.e1d24","type":"debug","z":"76ac7f35.d8caf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1350,"y":540,"wires":[]},{"id":"e7c37f26.2328b","type":"ui_button","z":"76ac7f35.d8caf","name":"","group":"20aa8e9b.366fa2","order":4,"width":0,"height":0,"passthru":false,"label":"button","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":350,"y":380,"wires":[["969947c.24153b8"]]},{"id":"6f5b3102.99a3e","type":"join","z":"76ac7f35.d8caf","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":430,"y":600,"wires":[["5ba4007b.6ae5"]]},{"id":"5ba4007b.6ae5","type":"change","z":"76ac7f35.d8caf","name":"","rules":[{"t":"set","p":"selected","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":600,"wires":[["7536dea2.747"]]},{"id":"969947c.24153b8","type":"change","z":"76ac7f35.d8caf","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"selected","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":380,"wires":[["fd822d5e.a19e4","d8403259.7fa23","ecf47e7f.91ae2"]]},{"id":"fd822d5e.a19e4","type":"debug","z":"76ac7f35.d8caf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":670,"y":480,"wires":[]},{"id":"d8403259.7fa23","type":"ui_text","z":"76ac7f35.d8caf","group":"20aa8e9b.366fa2","order":6,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":710,"y":380,"wires":[]},{"id":"7536dea2.747","type":"debug","z":"76ac7f35.d8caf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":770,"y":600,"wires":[]},{"id":"72d192cd.7a26fc","type":"ui_text_input","z":"76ac7f35.d8caf","name":"","label":"ArtistId","tooltip":"","group":"20aa8e9b.366fa2","order":3,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"S1","x":280,"y":600,"wires":[["6f5b3102.99a3e"]]},{"id":"e74ae73.f034618","type":"comment","z":"76ac7f35.d8caf","name":"Inserimento Id Artista","info":"","x":320,"y":540,"wires":[]},{"id":"7161ee7a.c805f","type":"comment","z":"76ac7f35.d8caf","name":"Metto il flow.select in msg.payload","info":"","x":440,"y":320,"wires":[]},{"id":"2bb9331e.b8217c","type":"sqlitedb","z":"","db":"C:\\Users\\pietr\\Desktop\\DAtabase\\chinook.db","mode":"RWC"},{"id":"6ee5fcdd.731954","type":"ui_group","z":"","name":"Default","tab":"6c1a6c45.3e57e4","disp":true,"width":"6","collapse":false},{"id":"20aa8e9b.366fa2","type":"ui_group","z":"","name":"Other-UI","tab":"6c1a6c45.3e57e4","disp":true,"width":"6","collapse":false},{"id":"6c1a6c45.3e57e4","type":"ui_tab","z":"","name":"Other DB","icon":"dashboard","disabled":false,"hidden":false}]

ITS-ICT

.

Clone this wiki locally