CODEWITHSUNDEEP
sqlsqlitedatabase-designsubqueryone-to-many
Manin
Manin

Reputation: 29

One to many relation and subqueries

I have a table persons(id, fist, last) and another table properties(person_id, property_name, property_value).

Each person may have many (undefined) properties, the name of these properties can vary based on the favorites of users. For example, for these records, we want the following output:

properties
==========
person_id  property_name  property_value
----------------------------------------
1          Gender         Male
1          Education      Under

person
======
id  first  last
----------------
1   John   Smith

result
======
id  First  Last   Gender  Education
-----------------------------------
1   John   Smith  Male    Under

Is there an easy way to have this done without using several steps in querying the db? I mean using subqueries, join, group by, or any other means necessary to do the job?

PS.I am using sqlite2&3

Thank you,

Mahmoud

Upvotes: 0

Views: 266

Answers (1)

SQB
SQB

Reputation: 4078

You can do 

SELECT
  pers.id,
  pers.first,
  pers.last,
  gender.property_value AS gender,
  edu.property_value AS education
FROM person pers
LEFT JOIN properties gender 
  ON (gender.person_id = pers.id AND gender.property_name = 'Gender') 
LEFT JOIN properties edu
  ON (edu.person_id = pers.id AND edu.property_name = 'Education')

But there is no way to do this for an arbitrary number of columns. You need to know which columns you want and join accordingly.

What you can do, is build a query dynamically beforehand in another language, and then execute that. If you do so, please use parameters. Please?

If you want, you can create a view showing all properties for person_ids:

CREATE VIEW all_properties (
  person_id,
  gender,
  education
)
AS SELECT
  pers.person_id,
  gender.property_value,
  edu.property_value
FROM (SELECT DISTINCT person_id FROM properties) pers
LEFT JOIN properties gender 
  ON (gender.person_id = pers.person_id AND gender.property_name = 'Gender') 
LEFT JOIN properties edu
  ON (edu.person_id = pers.person_id AND edu.property_name = 'Education')

But that's not going to make much difference.

Upvotes: 2

Related Questions