Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Self join with count (XP/2000)

    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 <font color=red>where there is only 1 matching record</font color=red> . 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).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Self join with count (XP/2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •