Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Relationship between multiple items?

    Hi guys, i need to verify multiple fields from one table against one field from another table. How can i do this?

    Here is a picture to illustrate:

    http://img202.imageshack.us/img202/3227/problem2d.jpg

    I need to verify all the values from the left table (except the primary key) against the field "itemId" from the right table.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "verify against" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give an example to illustrate what you have and what results you expect? A good example can be very helpful for us to understand your problem.

    It seems like you are programming for a game. What game is it?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2003
    Posts
    167
    If you mean just check to see if any fields have changed, I like to use binary_checksum() on the fields of interest with a full outer join on the two binary_checksums to see if any record between two tables has changed. However, if one of your tables is the audit standard and you just need to check the other for changes, you can just left outer join on the binary_checksum.

    BINARY_CHECKSUM (Transact-SQL)
    Kit Lemmonds

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait, i think i know what he means by "verify"

    it's a manual foreign key check

    every column in the left table is supposed to be a foreign key to the items table on the right

    and i'll bet there were no explicit FOREIGN KEYs defined, so there's bad data in the left table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2011
    Posts
    63
    Sorry i am new to databases....

    By verify i mean that no value in any of the mentioned fileds in the left table can exist without a match in the "itemId".

    So if i itemId has in it 5, 10, 15 and 20,

    Helmt cant contain 21.

    Hope you understand, thanks!

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Read the Books Online article entitled EXCEPT and INTERSECT (Transact-SQL).

    EXCEPT and INTERSECT (Transact-SQL)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TheGateKeeper View Post
    no value in any of the mentioned fileds in the left table can exist without a match in the "itemId".
    yeah, that's what i thought
    Code:
    SELECT charid
         , 'helmet' AS equipment_item
         , helmet  AS bad_item_reference
      FROM equipment
    LEFT OUTER
      JOIN items
        ON items.itemid = equipment.helmet
     WHERE items.itemid IS NULL
    UNION ALL
    SELECT charid
         , 'weapon'
         , weapon  
      FROM equipment
    LEFT OUTER
      JOIN items
        ON items.itemid = equipment.weapon
     WHERE items.itemid IS NULL
    UNION ALL
    ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2011
    Posts
    63
    Thanks for the code, but doesnt JOIN simply create a new result by combining those 2 fields?

    I want it to create a relationship so all values are verified.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TheGateKeeper View Post
    Thanks for the code, but doesnt JOIN simply create a new result by combining those 2 fields?
    i'm sorry, i don't understand this question

    LEFT OUTER JOIN with an IS NULL test is a conventional way of finding rows which don't have a match

    Quote Originally Posted by TheGateKeeper View Post
    I want it to create a relationship so all values are verified.
    the relationship involves declaring FOREIGN KEYs

    you won't be able to declare them if there is invalid data present

    did you try my query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by r937 View Post
    i'm sorry, i don't understand this question

    LEFT OUTER JOIN with an IS NULL test is a conventional way of finding rows which don't have a match

    the relationship involves declaring FOREIGN KEYs

    you won't be able to declare them if there is invalid data present

    did you try my query?
    I didnt try it yet as i am not at home.

    The databases is totally empty, so invalid data is not a problem. Does your query create a constraint or just check the values?

    I want a constraint like i have in the other tables, such as this:

    ALTER TABLE [MonsterDrops] ADD CONSTRAINT [Items_MonsterDrops]
    FOREIGN KEY ([itemId]) REFERENCES [Items] ([itemId])
    GO

    Maybe you misunderstood the question?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TheGateKeeper View Post
    Maybe you misunderstood the question?
    maybe... or perhaps you misstated it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by r937 View Post
    maybe... or perhaps you misstated it
    Yes probably, its not easy to explain.

    So how do i do it?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just like you said...
    Code:
    ALTER TABLE MonsterDrops 
    ADD CONSTRAINT Items_MonsterDrops 
    FOREIGN KEY ( itemId ) REFERENCES Items ( itemId )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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