SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Validate data based on another table

    Hello all,
    Hoping someone can help me w/ this select.


    I have table that hold attribute_value_id(s) for a given attribute_id
    attribute_value_id is the PK for the table.

    attribute_values table

    attribute_value_id attribute_id
    1 A
    2 A
    3 A
    4 A
    5 B
    6 B
    7 B
    8 c
    9 c
    10 c

    There is another table that holds a user_id, and attribute_id for that user and an attribute_value_id.

    user_attributes table

    user_id attribute_id attribute_value_id
    U1 A 1
    U1 B 5
    U1 C 8
    U2 A 2
    U2 B 6
    U2 C 9

    Since based on the attribute_values table - I know valid values for each attribute_id
    How can I check that the user_attributes table has a valid attribute_value_id for the given attribute_id
    For example this would not be a valid row
    U2 A 6
    b/c 6 isn't a value of attribute A

    What I after is a select that I can run on the user_attributes table
    that will tell me if the attribute_value_id is not a value for the attribute_id


    Thanks
    -Rob

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ua.user_id 
         , ua.attribute_id 
         , ua.attribute_value_id
      FROM user_attributes AS ua
    LEFT OUTER
      JOIN attribute_values AS a
        ON a.attribute_value_id = ua.attribute_value_id
       AND a.attribute_id       = ua.attribute_id
     WHERE a.attribute_value_id IS NULL
    returns all invalid rows in ua table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect - exactly what I needed.

    Thanks for the help!


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
  •