Professional Web Applications Themes

How to check the relationship between two tables, using a third table as a guide? - Microsoft SQL / MS SQL Server

Hi everyone, I have no idea how feasible this query is, so please bear with me. There are three tables I need to query against: call them Table1, Table2 and Table12. Table1 has a column named "PartNumber", while Table2 has one named "MachineNumber". Table12 contains both "PartNumber" and "MachineNumber" columns. Think of Table12 as the "official" guide as to which MachineNumber a given PartNumber belongs to. I'll show below that the guide has been ignored sometimes, and I have to figure out how widespread the problem is. Table2 and Table1 have a parent-child relationship, where one entry in Table2 can ...

Sponsored Links
  1. #1

    Default How to check the relationship between two tables, using a third table as a guide?

    Hi everyone,

    I have no idea how feasible this query is, so please bear with me.

    There are three tables I need to query against: call them Table1, Table2 and
    Table12.

    Table1 has a column named "PartNumber", while Table2 has one named
    "MachineNumber". Table12 contains both "PartNumber" and "MachineNumber"
    columns. Think of Table12 as the "official" guide as to which MachineNumber
    a given PartNumber belongs to. I'll show below that the guide has been
    ignored sometimes, and I have to figure out how widespread the problem is.

    Table2 and Table1 have a parent-child relationship, where one entry in
    Table2 can have multiple linked entries in Table1. The linking field is
    Table2's primary key, which is _not_ the same as the MachineNumber field.

    I think that's what's cooked us; no integrity checks between MachineNumber
    and PartNumber. Oh, who am I kidding-- that's exactly what's done us in,
    and now I have to figure out how many records in Table1 are linked to the
    wrong Table2 entries.

    I'm trying to formulate the query, but I'm having trouble tying the three
    tables together. In a nutshell, I need to loop through Table1's records,
    look up the linked MachineNumber from Table2, and compare against Table12 to
    see if it's an appropriate match.

    In other words, for any given record in Table1, does its PartNumber
    "correctly" match up with Table2's MachineNumber, with Table12 acting as the
    referee?


    Sponsored Links
    Jim Guest

  2. #2

    Default Re: How to check the relationship between two tables, using a third table as a guide?

    Please provide the DDL for your tables. It was an interesting narrative, but
    it left unanswered several questions regarding your datatypes and keys. For
    the sake of bandwidth, only include the relevant columns, but DO include the
    primary and foreign key constraints, and any unique indexes.

    Some sample data would help as well. You should supoply it in the form of
    insert statements:
    insert table1 values(...)

    Bob Barrows

    Jim Bancroft wrote: 



    Bob Guest

  3. #3

    Default Re: How to check the relationship between two tables, using a third table as a guide?

    Hi

    Also...

    You may want to try the second derived table with a NOT IN the first derived
    table.

    John

    "John Bell" <com> wrote in message
    news:3f2cbed5$0$15038$news.pipex.net... 
    DT1 
    > and 
    > MachineNumber [/ref]
    is. [/ref]
    field. [/ref]
    MachineNumber [/ref]
    in, [/ref]
    the [/ref]
    three [/ref]
    records, [/ref]
    Table12 
    > the 
    >
    >[/ref]


    John Guest

  4. #4

    Default Re: How to check the relationship between two tables, using a third table as a guide?

    i would do something like this:

    SELECT COUNT(*) AS MissedMatches
    FROM (
    SELECT t1.ID
    , t1.PartNumber
    , t2.MachineNumber
    FROM Table1 t1
    JOIN Table2 t2
    ON t1.P_ID = t2.ID
    ) linked
    LEFT JOIN Table12 referee
    ON linked.PartNumber = referee.PartNumber
    AND linked.MachineNumber = referee.MachineNumber
    WHERE referee.ID IS NULL -- no match found

    (untested, post DDL for more)

    HTH,
    </wqw>

    "Jim Bancroft" <bobbygambles_at_nospam.msn.com> wrote in message
    news:phx.gbl... 
    and 
    MachineNumber 
    to 
    the 


    Vlad Guest

  5. #5

    Default Re: How to check the relationship between two tables, using a third table as a guide?

    Thanks everyone-- I think I've got enough to go forward on this now.
    Appreciate the tips.

    -Jim


    Jim Guest

Similar Threads

  1. Table problem: my guide lines have vanished
    By flapdoodle9550 in forum Macromedia Dynamic HTML
    Replies: 6
    Last Post: June 8th, 08:18 PM
  2. Check Parent-child relationship in datagrid
    By Grey in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: March 18th, 07:43 PM
  3. N to M relationship table and indexes
    By Luc in forum Oracle Server
    Replies: 4
    Last Post: October 20th, 11:36 PM
  4. Db2 tables consistency check
    By Stefano in forum IBM DB2
    Replies: 8
    Last Post: September 12th, 05:09 AM
  5. Can it have dependences without a relationship among the tables?
    By Frank Dulk in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 07:08 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139