Need help with best technique for this retrieval...

Dev Shed Forums Sponsor:

February 5th, 2013, 05:20 AM

jt-developer

Registered User

Join Date: Dec 2003

Posts: 19

Time spent in forums: 11 m 50 sec
Reputation 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

February 5th, 2013, 05:35 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,853

Time spent in forums: 1 Month 2 Weeks 1 Day 13 h 56 m 19 sec
Reputation Power: 813

Hi,

Quote:

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.

February 5th, 2013, 05:47 AM

jt-developer

Registered User

Join Date: Dec 2003

Posts: 19

Time spent in forums: 11 m 50 sec
Reputation Power: 0

Quote:

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?

February 5th, 2013, 05:57 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,853

Time spent in forums: 1 Month 2 Weeks 1 Day 13 h 56 m 19 sec
Reputation Power: 813

Quote:

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.

Quote:

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 = ...

February 5th, 2013, 07:20 AM

jt-developer

Registered User

Join Date: Dec 2003

Posts: 19

Time spent in forums: 11 m 50 sec
Reputation Power: 0

Quote:

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.

Need help with best technique for this retrieval...

Developer Shed Advertisers and Affiliates