#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    19
    Rep Power
    0

    Need help with best technique for this retrieval...


    Hi all. I have a table that contains records which are related to various multiple ids, ie record #1 is related to id 10,11,12 and 51,52,53. Currently the data is being stored as follows

    ID | Record Name | Related ID
    1 | some name | 10-12&51-53

    ...the Related ID column you'll notice I've written using a "-", meaning "from A-B", and a "&" meaning "and".

    Is there any way I can search this table and find a related ID that is within that group, id find Record Name where Related ID=11?

    thanks all
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Hi,

    Originally Posted by jt-developer
    Is there any way I can search this table and find a related ID that is within that group, id find Record Name where Related ID=11?
    Nope, because what you're doing there isn't how the relational model works.

    The first thing you need to do is repair your data according to the first normal form. After that, you can start querying your database.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    19
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,



    Nope, because what you're doing there isn't how the relational model works.

    The first thing you need to do is repair your data according to the first normal form. After that, you can start querying your database.
    Thanks, so I just need to pull those id lists out and insert them as single lines in another table such as 'associated_ids'? Makes sense, but I'm not sure what the mysql call would be to retrieve multiple rows relating to one id. Do you know what syntax I should be using for that?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Originally Posted by jt-developer
    Thanks, so I just need to pull those id lists out and insert them as single lines in another table such as 'associated_ids'?
    Yes.



    Originally Posted by jt-developer
    Makes sense, but I'm not sure what the mysql call would be to retrieve multiple rows relating to one id. Do you know what syntax I should be using for that?
    Not sure what you mean by that. It's a standard query:
    Code:
    SELECT
    	related_id
    FROM
    	related_ids
    WHERE
    	record_id = ...
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    19
    Rep Power
    0
    Originally Posted by Jacques1
    Not sure what you mean by that. It's a standard query:
    Code:
    SELECT
    	related_id
    FROM
    	related_ids
    WHERE
    	record_id = ...
    Sorry, was thinking a bit to far ahead (the actual sql is part of a larger search).

    thanks for the info.

IMN logo majestic logo threadwatch logo seochat tools logo