Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    13

    Unanswered: Need a little help on joining two tables

    I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.

    First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.

    Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.

    Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.

    Example code:
    select groups.group.id, groups.group.name
    from groups, users
    where groups.disabled='1'
    and users.user_id = $user_id
    and groups.group.id ? users.group.id

    The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?

    Thanks in advance for any input.
    T

  2. #2
    Join Date
    Jan 2004
    Posts
    40
    do you mean you want

    PSEUDO CODE
    select groups.group.id, groups.group.name
    from groups, users
    where groups.disabled='1'
    and users.user_id = $user_id
    and groups.group.id IN users.group.id

  3. #3
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by fattyacid
    do you mean you want

    PSEUDO CODE
    select groups.group.id, groups.group.name
    from groups, users
    where groups.disabled='1'
    and users.user_id = $user_id
    and groups.group.id IN users.group.id
    I thought about the IN statement but I do not think that will work. The data in the groups.group.id column will be a single integer say the number 3. Where the data in the user.group.id column could be multiple intergers say 1, 3, 5, 10, etc...

    does that make sense?

    Thanks
    T

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here, read this....I've add it to my favortites

    http://www.sqlteam.com/forums/topic....udf,csv,string
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    40
    you can try patindex or charindex

    so something like
    select groups.group.id, groups.group.name
    from groups, users
    where groups.disabled='1'
    and users.user_id = $user_id
    and patindex('%' + groups.group.id + ',%', users.group.id) <> 0

    but it seems like the best way is to not have a list of integers in the user database. You can't seperate the list of integers into seperate records?

  6. #6
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by fattyacid
    you can try patindex or charindex

    so something like
    select groups.group.id, groups.group.name
    from groups, users
    where groups.disabled='1'
    and users.user_id = $user_id
    and patindex('%' + groups.group.id + ',%', users.group.id) <> 0

    but it seems like the best way is to not have a list of integers in the user database. You can't seperate the list of integers into seperate records?
    Ok thanks Brett and fatty I will see what I can do.

    T

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I highly recommend Jeffs udf...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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