CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 939

Can I display one to many results in results table?

I have a table for person and a table for language. Each person can speak up to 4 languages. My client wants to search for people that can speak, for instance Spanish.

My problem is that the results table currently won't have a column called Language because they will speak more than one. I could display just the first one, but it will be misleading to hide the other languages that they speak.

The table could have a column for each language, and fill in NULL if they don't have all 4 languages, i.e.:

Language 1
Language 2
Language 3
Language 4

But this seems very sloppy.

I have considered listing all of the languages in a single column, using a comma separated list, but this is know good for sorting the column alphabetically.

Currently, I am having to tell my client that the results table can only show columns where the person has one of them (1 to 1), i.e. name, location, native language etc. Only when the client clicks on that person, can it reveal all of their languages.

Does anyone know if there is a common way to solve this? Hope this makes sense

I do have an association table. The problem is that my search will return

joe bloggs, gotham city, spanish

then

joe bloggs, gotham city, french

on the next row - but then the same person is listed twice in the table. When I restrict it to one entry per name, I just get "joe bloggs, gotham city, spanish". Now I don't know that he also speaks french. Is this clearer?

Upvotes: 1

Views: 462

Answers (4)

No'am Newman
No'am Newman

Reputation: 6477

Having thought about this some more, it seems that you have the following options

  1. Use the simple join/link/association table that both Christophe and I suggested, then massage the data in the program which calls the SQL. This is the simplest route to take.
  2. Maintain a comma delimited list of languages along with the join table. The join table will allow you to answer queries such as 'who speaks Spanish', whereas the list will enable you to print out a list of languages per person. Maintaining this list will be a problem.
  3. Use the 'dedicated field per language' approach of Ravindra; every time you need a new language, you will have to change the field structure, thus this approach is very much not recommended.
  4. Use a cross tab query; this is quite hard to do and depends on which flavour of SQL you are using. Look at this article creating-cross-tab-queries-and-pivot-tables-in-sql.

In my option, option 1 is the best and easiest. SQL is great at what it does and terrible at what it doesn't do, so it's best to take the best parts of SQL and surround them with the best parts of a declarative language.

Upvotes: 0

No'am Newman
No'am Newman

Reputation: 6477

You should have a join table which contains two fields: person and language. There should be one row per person per language, so if one person speaks four languages, there would be four rows for this user.

The primary key for this table would comprise both fields.

Then to get a list of which people speak Spanish, you would need a query like

select people.name
from people inner join p2l
on people.id = p2l.person
inner join languages
on p2l.language = languages.id
where languages.name = 'Spanish'

And a list of all people who speak a language

select people.name, languages.name
from people inner join p2l
on people.id = p2l.person
inner join languages
on p2l.language = languages.id

And now a list of all people, whether they speak a language or not

select people.name, languages.name
from people left join p2l
on people.id = p2l.person
inner join languages
on p2l.language = languages.id

Upvotes: 1

Ravindra Bagale
Ravindra Bagale

Reputation: 17655

create two table
person having cloumns- person_id,person_name & more if u want.
create another table -Language having columns-person_id,lang1,lang2,lang3,lang4
make person_id as foreign key
now when u want to access languages, just fetch them by compairing person_id

 SELECT p.person_id
FROM person p, language l
WHERE p.person_id = l.person_id;

Upvotes: 0

Christophe Biocca
Christophe Biocca

Reputation: 3468

So the traditional way to solve this is to create something called an association table, which has foreign keys to both the person and language tables. This lets you add an arbitrary number of languages (including zero). Then you can join accross these tables to find all users speaking a specific language.

This may help with how to structure databases: http://en.wikipedia.org/wiki/First_normal_form There's higher normal forms, but this will help you solve your current problem.

Upvotes: 0

Related Questions