[RESOLVED] one-to-many relationship woes

[JLHeidecker]

Table 1: ID, item
Table 2: ID, tag

the one-to-many relationship goes from Table 1 to Table 2, i.e. there are many tags for each item.

when I query the database for a particular item, i wish it would return ONE record for that item. but alas, if I have 4 tags for that item, then i get 4 records returned.

this is how mysql works of course.

but how can i get around this? i want all the tags to come with it, but I only want one record returned, so my output would be:

item, tag1, tag2, tag3, tag4

Instead of

item, tag, item, tag, item, tag, item, tag

It seems like there should be a way to take advantage of this wonderful relational database architecture and return/update/delete just the data I want (i.e. it should know how to follow the relational/foreign keys)

Know what i mean dudes?? 😕

[laserlight]

For updating there should be no problem as you should be able to update a tag independently of its item, assuming that your database is sufficiently normalised. For deleting, you may want to use ON DELETE CASCADE so that the deletion of an item would cause the deletion of its tags.

For selection, I do not think that there is a way to turn the rows from the tags table into columns in a result set. However, the problem can be solved with PHP by having an array of items, and each item has an array of tags. This could be done using objects, or with a two dimensional array indexed by item name.

[Sxooter]

JLHeidecker wrote:

Table 1: ID, item
Table 2: ID, tag

the one-to-many relationship goes from Table 1 to Table 2, i.e. there are many tags for each item.

when I query the database for a particular item, i wish it would return ONE record for that item. but alas, if I have 4 tags for that item, then i get 4 records returned.

this is how mysql works of course.

but how can i get around this? i want all the tags to come with it, but I only want one record returned, so my output would be:

item, tag1, tag2, tag3, tag4

Instead of

the good news is there is a way to do it. Bad news, it's a PostgreSQL way...

you can use a construct like:

array_to_string(array(select ltb_avg from ltb_threshold t1 where t1.a_pcc=k.a_pcc),',') to do it. Here's an example:

select distinct a_pcc, array_to_string(array(select ltb_avg from ltb_threshold t1 where t1.a_pcc=k.a_pcc),',') from ltb_threshold as k;

ABC | 1.62,2.88,1.00,6.33,2.33, ...
DEF | 147.46,155.11,167.99,222.81,220.59, ...

Not at all sure how to make MySQL do it. I think you'll have to do it in php.

[ahundiak]

Hi Sxooter,

That looks pretty neat. Will this method work when you have two 1:n relations?

person: person_id, fname, lname
phone: phone_id, phone_type_id, person_id, phone_number
email: email_id, email_type_id, person_id, email_address

So a person can have several phone numbers and emails and I want to get one record back with the person name as well as their phone numbers and emails.

No need to provide the exact syntax. Just wondering if it's possible.

[cgraz]

If I understood correctly, you can use group_concat()

select
   id
  , group_concat(tags)
 from <your_table>
 ...
group
  by id

Will return a comma separated list of tags for each id.

You can even specify different separators: group_concat(tags SEPARATOR '<br />')

[JLHeidecker]

ahundiak 🙂

In case my original post wasn't clear, ahundiak captured my concern as well.

As I've been learning about relational databases, it has surprised me that this functionality does not exist in MySQL. When I pull a record, say, a "person" in ahundiak's example, there should be a way to request that mysql follow the foreign keys I defined (person_id in ahundiak's example) and return data from all tables in one record (or update or delete).

otherwise, what's the point in defining a foreign key anyway? when is that relationship ever looked at by the mysql software?

[laserlight]

otherwise, what's the point in defining a foreign key anyway? when is that relationship ever looked at by the mysql software?

Remember, even without what Sxooter and cgraz suggested, you can still retrieve the related rows by using the foreign key, just that it would not be as convenient. Aside from that, the foreign key constraint enforces that there cannot be a tag without an item, thus maintaining referential integrity in the event that an attempt is made to delete an item that has existing tags.

[JLHeidecker]

thanks laserlight.

so... MySQL will use the foreign key to ensure data integrity across the tables, but it won't help me simplify the queries and PHP I need to write to select/update/delete the records.

thanks for clearing that up! (please correct me if i have this wrong!)

thanks

[pete_bisby]

You do need to look at how to apply foreign keys to a MySQL table - Chapter 14.2.6.4 in the MySQL Manual

Currently, InnoDB-defined tables are the only ones that will allow foreign keys. This can be done by adding the CONSTRAINT ... FOREIGN KEY to a table definition.

If you're using phpMyAdmin, make sure you have applied an index to the field you wish to have as a foreign key. Then click on the "Relation View" on the Table Defintion screen to define the foreign key constraints.

As far as relational databases are concerned, if you have parent/child relationships (e.g. 1 parent record and 3 child records) you will always return 3 records with the parent record repeated 3 times. Removing the duplicated information is a formatting issue, not a database issue, and IMHO should remain that way.

Let the database do the job it as intended to do - let it perform more than it needs could impact on performance.

[pete_bisby]

You do need to look at how to apply foreign keys to a MySQL table - Chapter 14.2.6.4 in the MySQL Manual

Currently, InnoDB-defined tables are the only ones that will allow foreign keys. This can be done by adding the CONSTRAINT ... FOREIGN KEY to a table definition.

If you're using phpMyAdmin, make sure you have applied an index to the field you wish to have as a foreign key. Then click on the "Relation View" on the Table Defintion screen to define the foreign key constraints.

As far as relational databases are concerned, if you have parent/child relationships (e.g. 1 parent record and 3 child records) you will always return 3 records with the parent record repeated 3 times. Removing the duplicated information is a formatting issue, not a database issue, and IMHO should remain that way.

Let the database do the job it as intended to do - let it perform more than it needs could impact on performance.

[JLHeidecker]

pete_bisby wrote:

Removing the duplicated information is a formatting issue, not a database issue, and IMHO should remain that way.

That really nails the point here. thanks!

[Sxooter]

In pgsql I'd do something like this:

select 
  id,
  array_to_string(array(select tag from table2 t2 where t2.id=t1.id),',') as mytags
from table1 t1

Can you do that with group_concat in mysql?