Get details from another mysql table

Question

Hi,

I have a table which would contain information about a certain month, and one column in that row would have mysql row id's for another table in it to grab multiple information from

is there a more efficent way to get the information than exploding the ids and doing seperate sql queryies on each... here is an example:

Row ID | Name | Other Sources
1        Test   1,2,7

the Other Sources has the id's of the rows from the other table which are like so

Row ID | Name | Information  | Link
1        John | No info yet? | http://blah.com
2        Liam | No info yet? | http://blah.com
7        Steve| No info yet? | http://blah.com

and overall the information returned wold be like the below

Hi this page is called test... here is a list of our sources
- John (No info yet?) find it here at http://blah.com
- Liam (No info yet?) find it here at http://blah.com
- Steve (No info yet?) find it here at http://blah.com

i would do this... i would explode the other sources by , and then do a seperate SQL query for each, i am sure there could be a better way?

Answer 1

Not easily with your table structure. If you had another table like:

ID    Source
1     1
1     2
1     7

Then join is your friend. With things the way they are, you'll have to do some nasty splitting on comma-separated values in the "Other Sources" field.

Answer 2

Maybe I'm missing something obvious (been known to), but why are you using a single field in your first table with a comma-delimited set of values rather than a simple join table. The solution if do that is trivial.

Answer 3

The problem with these tables is that having a multi-valued column doesn't work well with SQL. Tables in this format are considered to be normalized, as multi-valued columns are forbidden in First Normal Form and above.

First Normal Form means...

1. There's no top-to-bottom ordering to the rows.
2. There's no left-to-right ordering to the columns.
3. There are no duplicate rows.
4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

—Chris Date, "What First Normal Form Really Means", pp. 127-8[4]

Anyway, the best way to do it is to have a many to many relationship. This is done by putting a third table in the middle, like Dominic Rodger does in his answer.

Answer 4

Looks like a classic many-to-many relationship. You have pages and sources - each page can have many sources and each source could be the source for many pages?

Fortunately this is very much a solved problem in relational database design. You would use a 3rd table to relate the two together:

Pages (PageID, Name)
Sources (SourceID, Name, Information, Link)
PageSources (PageID, SourceID)

The key for the "PageSources" table would be both PageID and SourceID.

Then, To get all the sources for a page for example, you would use this SQL:

SELECT s.* 
FROM Sources s INNER JOIN PageSources ps ON s.SourceID = ps.SourceID
AND ps.PageID = 1;