Self join with count (XP/2000)

Topic

New Reply

I’m having trouble with a query.

I have a table (Pipes) where each record is a pipe in a water supply network. Each pipe has an ID (FID) plus the ID of the points where it starts from (FNODE) and goes to (TNODE).

Some of the pipes in the network are missing attribute information eg diameter (DIAM).

Pipes where information is missing need to get it from adjacent pipes. Previously, code had been written to loop through a recordset and compare both the FNODE and TNODE a pipe with the FNODE and TNODE of all the other pipes. This appeared to work but contained a logic error (that’s not easy to explain).

I have been thinking about this for ages and the only appropriate solution seems to be as follows:

Step 1 For each pipe where the diameter is unknown attempt to match the TNODE with the FNODE for the other pipes ie attempt to find pipes downstream.
Step 2 For each pipe where the diameter is unknown attempt to match the FNODE with the TNODE for the other pipes ie attempt to find pipes upstream.

In each case, a successful ‘match’ is where there is only 1 matching record . This is the critical bit.

Rather than looping through a recordset I think this can be achieved with a query (well, 2 similar queries). I’ve been trying to create a self join query using the table and a copy of the table. What I can’t figure out is how use the number of matched records as a criteria.

Attached is a sample db containing an extract of a pipe table. Unfortunately, none of the pipes as missing the diameter (or other) attributes but there are potential multiple matches (which is the issue I’m struggling with).

Reply | Quote

Replies

The following query will return all FIDs for which DIAM is missing (null) and for which there is exactly one match with a non-missing DIAM if you match on TNODE vs FNODE:

SELECT Pipes.FID
FROM Pipes INNER JOIN Pipes AS Pipes_1 ON Pipes.TNODE = Pipes_1.FNODE
WHERE Pipes.DIAM Is Null AND Pipes_1.DIAM Is Not Null
GROUP BY Pipes.FID
HAVING Count(Pipes_1.DIAM)=1

In your sample database, this query will return no records because there are no missing DIAMs, but if I clear a few DIAMs, the query will show the appropriate FIDs. To display the matching DIAM too:

SELECT Pipes.FID, Pipes_1.DIAM
FROM Pipes INNER JOIN Pipes AS Pipes_1 ON Pipes.TNODE = Pipes_1.FNODE
WHERE Pipes.DIAM Is Null AND Pipes_1.DIAM Is Not Null
GROUP BY Pipes.FID, Pipes_1.DIAM
HAVING Count(Pipes_1.DIAM)=1

Reply | Quote