CODEWITHSUNDEEP
mysql
scruffycoder86
scruffycoder86

Reputation: 549

MySQL create one row from two or more resultsets

I have a table that has a list of persons with the following fileds:

persons (id, identity, name, surname)

And then there is a second table with the following fields:

identities (id, type)

The values for the identities.type field could sexuality or affiliation in terms of whether a person is a traditional healer or not.

We have a third table where we have persons identity preferences and below is the mapping:

preferences (id, person_id, identity_id, value)

Say for example I identify as a Heterosexual guy and a traditional healer and then person 2 identifies as just homosexual.

Below is a representation of the scenario above:

enter image description here

If I want to eliminate the second row and add the second value field for the first user in the first row, is it possible?

Upvotes: 0

Views: 30

Answers (2)

Elshad Aghazade
Elshad Aghazade

Reputation: 76

In the persons and identities table you have to insert every record just one time. But in the preferences table you can insert any combination of previous two tables how many times you want. Than you have to write a select statement with joins to display a table like in the picture:

SELECT * FROM preferences p
INNER JOIN persons u on u.id = p.person_id
INNER JOIN identities i on i.id = p.identity_id

Upvotes: 0

siax
siax

Reputation: 150

A bit difficult without seeing the tables separetly, but that's my guess based on your description. Does this go into the right direction?

SELECT 
p.id, p.name, p.surname, pref.id, pref.person_id,
CASE WHEN i.type = 'sexuality' THEN p.value ELSE NULL END AS sexuality,
CASE WHEN i.type = 'affiliation' THEN p.value ELSE NULL END AS affiliation
FROM persons p
JOIN preferences pref ON p.id = pref.person_id
JOIN identities i ON i.id = pref.identity_id

Upvotes: 1

Related Questions