tags:

views:

67

answers:

4

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?

+1  A: 

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.

Dominic Rodger
your way seems like an alternative, is there not a way to avoid creating another table?
Yes, what you're doing already. There are ways to split up comma-separated values in SQL, but those roads lead to sadness.
Dominic Rodger
@johnnnnnnnnny - There's really no other good alternative. This is a very common pattern in database design, and this is pretty much the one-and-only way it is done :)
Eric Petroelje
A: 

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.

Zac
what do you mean? the first table will have lots of rows with different sources
Dominic gave a far more useful answer, which covers where I was going.
Zac
A: 

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.

R. Bemrose
+4  A: 

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;
Eric Petroelje
+1 - more detailed and helpful answer than mine!
Dominic Rodger
what sql would make it possible to echo out results in all two tables?