vendredi 14 août 2015

how to query from an intermediate table

i am using sqlite with java JDBC. i created the below tables in the data base. they are 3 tables "node, tag and nodeTag". the relation between node and "tag" is M:M. so i created an intermediate table "nodeTag". the PK of "node table" is the columns "lat,lng", and the PK of "tag table" is an auto incremented primary key.

my question is, should the intermediate table "nodeTag" contain the primary keys of the "node" table and "tag" table to be as follows?

[pk: 1, lat: 700, lng: 20, tagPK]

and not as shown below in the output?

if yes, how can i query the "motorway" and "maxSpeed" info based on a given lat and lng?

11. [16-30-57.0076] d.NodeFactory -> selectAll: [rowID: 0, NodeID: 10, Lat: 700, Lng: 20]
12. [16-30-57.0076] d.NodeFactory -> selectAll: [rowID: 1, NodeID: 11, Lat: 701, Lng: 21]
13. [16-30-57.0076] d.NodeFactory -> selectAll: [rowID: 2, NodeID: 12, Lat: 702, Lng: 22]
14. [16-30-57.0076] d.NodeFactory -> selectAll: [rowID: 3, NodeID: 13, Lat: 703, Lng: 23]
15. [16-30-57.0076] d.NodeFactory -> selectAll: [rowID: 4, NodeID: 14, Lat: 704, Lng: 24]

17. [16-30-57.0076] d.TagFactory -> selectAll: [pk: 1, type: motorway_0, maxSpeed: 95]
18. [16-30-57.0076] d.TagFactory -> selectAll: [pk: 2, type: motorway_1, maxSpeed: 96]

20. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 1, lat: 700, lng: 20, type: motorway_0, maxSpeed: 95]
21. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 2, lat: 700, lng: 20, type: motorway_1, maxSpeed: 96]
22. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 3, lat: 701, lng: 21, type: motorway_0, maxSpeed: 95]
23. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 4, lat: 701, lng: 21, type: motorway_1, maxSpeed: 96]
24. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 5, lat: 702, lng: 22, type: motorway_0, maxSpeed: 95]
25. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 6, lat: 702, lng: 22, type: motorway_1, maxSpeed: 96]
26. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 7, lat: 703, lng: 23, type: motorway_0, maxSpeed: 95]
27. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 8, lat: 703, lng: 23, type: motorway_1, maxSpeed: 96]
28. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 9, lat: 704, lng: 24, type: motorway_0, maxSpeed: 95]
29. [16-30-57.0076] d.NodeTagFactory -> selectAll: [pk: 10, lat: 704, lng: 24, type: motorway_1, maxSpeed: 96]

Aucun commentaire:

Enregistrer un commentaire