5

I have a polygon layer with area geometries of parcels and tables with contracts, with owners / users linked by means of a link table in many to many relationships. This is because some parcels may have multiple owners and each owner may have multiple and different contracts to certain parcels. Can I, and if so how, do a query of a related layer/table in the expression editor or define a style so that I query attributes of a related table? For example, I would like to know or display how many parcels of a particular owner already have contracts. What is the correct expression for querying a value of another realted layer?

my exemplatory set up:

table: owner

fid owner
1 Miller
2 Andrews
3 ...

table: contracts

fid contract
1 20230901 (Hyperlink)
2 20190628 (Hyperlink)
3 ...

polygon: parcels

fid parcel
1 parcel 23
2 parcel 44/6
3 parcel 789
4 ...

link table: links

fid parcel owner contract
1 3 2 2
2 1 2 2
3 2 1 1
4 ... ... ...
Improve this question
7
  • sorry for the display of the tables. in edit mode it looks fine :/
    – Marek
    Commented Sep 11, 2023 at 8:44
  • 1
    the table requires a blank line before it
    – JGH
    Commented Sep 11, 2023 at 12:21
  • 2
    Is writing SQL an option for you? QGIS can compile and execute the SQLite/SpatiaLite dialect and functionality on most data sources narively. Use a Virtual Layer (from the Layer menu or from within the DB Manager) and creaate thematic layers as needed.
    – geozelot
    Commented Sep 13, 2023 at 19:29
  • if there is no other , sure it would be an option. I've never done this before, so i am quite unfamiliar with the procedure...
    – Marek
    Commented Sep 14, 2023 at 7:43
  • Can you elaborate a bit? It seems like the information you want is already in the links table. If you query the links table for owner 2, you would get parcels 1 & 3 both under contract 2. Do you want a table that has the actual owner name, parcel number and contract number/link? For example, if you query for owner miller, you would get parcels 23 & 44/6 both under contract 20190628?
    – jbalk
    Commented Sep 14, 2023 at 17:04

1 Answer 1

Reset to default
1

Use Join attributes by field value tool in QGIS. You will run it three times. First, set the input layer as the owner table, input layer 2 as the links table. Set table field to fid and table field 2 to owner. Set the join type to one-to-many. For Layer 2 field to copy, select parcel and contract. Create a temp layer.

Run the tool again. Set the input layer as the temp layer from the first run. Set the input layer 2 to parcels table. Set table field to parcel, table field 2 to fid. Set the join type to one-to-one. For Layer 2 field to copy, select parcel. Create a temp layer.

Run the tool again. Set the input layer as the temp layer from the second run. Set the input layer 2 to contracts table. Set table field to contract, table field 2 to fid. Set the join type to one-to-one. For Layer 2 field to copy, select contract. Save the output layer.

enter image description here

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.