[Dbix-class] JOINs gallery.

[Emanuele Zeppieri]

Sorry for my late.
Instead of posting JOINs involving dozens of tables, thus difficult to
read and to understand, I opted for the simplest db schema possible,
that is a single table, so that the JOINs should be easier to
understand.

The table (which lives under the "dbix_class" schema, in PostgreSQL
parlance) contains several family trees. Each record represents a
person, along with the pointers to his/her father and mother (and the
birth year):

CREATE TABLE dbix_class.person
(
  person_id int4,
  name varchar,
  father_id int4,
  mother_id int4,
  birth_year char(4),
)

The pointers to the parents point to other records in the same table
(that is, they contain other person_id values).

Attached to this message there is an (PostgreSQL) sql script to create
such table (and the enclosing dbix_class schema) and populate it with
some test values, so that you can run the queries and/or alter/extend
them if you like.
(Please remove any reference to the dbix_class schema both in the script
and in the queries if you don't want it).
The script and every query have been tested on PostgreSQL v.8.0.3.

The queries were deliberately made overly convoluted, of course.


Query 1
List the persons along with their parents.
Cascading JOINs version:
------------------------------------------
SELECT
	child.name, father.name, mother.name
FROM
	dbix_class.person AS child
	JOIN
	dbix_class.person AS father
	ON
		father.person_id = child.father_id
	JOIN
	dbix_class.person AS mother
	ON
		mother.person_id = child.mother_id
------------------------------------------


Query 2
The same as above but nesting one JOIN:
------------------------------------------
SELECT
	child.name, father.name, mother.name
FROM
	dbix_class.person AS mother
	JOIN (
		dbix_class.person AS child
		JOIN
		dbix_class.person AS father
		ON
		father.person_id = child.father_id
	)
	ON
	mother.person_id = child.mother_id
------------------------------------------


Query 3
Lists the persons along with their parents and their paternal
grandparents.
Shows nested JOINs followed by other cascading JOINs.
------------------------------------------
SELECT
	child.name, father.name, mother.name,
	paternal_grandfather.name, paternal_grandmother.name
FROM
	dbix_class.person AS mother
	JOIN (
		dbix_class.person AS child
		JOIN
		dbix_class.person AS father
		ON
		father.person_id = child.father_id
	)
	ON
	mother.person_id = child.mother_id

	JOIN

	dbix_class.person AS paternal_grandfather
	ON
	paternal_grandfather.person_id = father.father_id 
	

	JOIN

	dbix_class.person AS paternal_grandmother
	ON
	paternal_grandmother.person_id = father.mother_id
------------------------------------------


Query 4
The same as above, but nesting instead of cascading one of the JOINs (so
we have two nested levels).
------------------------------------------
SELECT
	child.name, father.name, mother.name,
	paternal_grandfather.name, paternal_grandmother.name
FROM
	dbix_class.person AS paternal_grandfather
	JOIN (
		dbix_class.person AS mother
		JOIN (
			dbix_class.person AS child
			JOIN
			dbix_class.person AS father
			ON
			father.person_id = child.father_id
		)
		ON
		mother.person_id = child.mother_id
	)
	ON
	paternal_grandfather.person_id = father.father_id 
	

	JOIN

	dbix_class.person AS paternal_grandmother
	ON
	paternal_grandmother.person_id = father.mother_id
------------------------------------------


Query 5
Lists all and only the persons who have children.
Shows a subquery inside the ON clause!
------------------------------------------
SELECT
	person_name.name
FROM
	dbix_class.person AS person_id
	JOIN
	dbix_class.person AS person_name
	ON
	person_id.person_id = person_name.person_id
		AND
	EXISTS (
		SELECT person_id
		FROM dbix_class.person
		WHERE
			father_id = person_id.person_id
			OR
			mother_id = person_id.person_id
	)
------------------------------------------


Query 6
Lists all and only the persons who do not have children.
The same as above plus a cascading RIGHT JOIN (to find the complement of
the table).
------------------------------------------
SELECT
	all_persons.name
FROM
	dbix_class.person AS person_id
	JOIN
	dbix_class.person AS person_name
	ON
	person_id.person_id = person_name.person_id
		AND
	EXISTS (
		SELECT person_id
		FROM dbix_class.person
		WHERE
			father_id = person_id.person_id
			OR
			mother_id = person_id.person_id
	)

	RIGHT JOIN

	dbix_class.person AS all_persons
	ON
	all_persons.person_id = person_id.person_id
WHERE
	person_name.name IS NULL
------------------------------------------


Query 7
Lists all the persons in the table by UNIONing those who have children
and those who don't have children (tertium non datur ;-)
Simply (so to say) a UNION of queries 5 and 6.
------------------------------------------
SELECT
	person_name.name
FROM
	dbix_class.person AS person_id
	JOIN
	dbix_class.person AS person_name
	ON
	person_id.person_id = person_name.person_id
		AND
	EXISTS (
		SELECT person_id
		FROM dbix_class.person
		WHERE
			father_id = person_id.person_id
			OR
			mother_id = person_id.person_id
	)

UNION

SELECT
	all_persons.name
FROM
	dbix_class.person AS person_id
	JOIN
	dbix_class.person AS person_name
	ON
	person_id.person_id = person_name.person_id
		AND
	EXISTS (
		SELECT person_id
		FROM dbix_class.person
		WHERE
			father_id = person_id.person_id
			OR
			mother_id = person_id.person_id
	)

	RIGHT JOIN

	dbix_class.person AS all_persons
	ON
	all_persons.person_id = person_id.person_id
WHERE
	person_name.name IS NULL
------------------------------------------


Query 8
Lists all the persons who don't have children and are born after 1982.
Shows a JOIN with a subquery inside the ON clause which in turn contains
another JOIN with another subquery inside its ON clause (shesh!)
------------------------------------------
SELECT young_person.name, young_person.birth_year
FROM

(
	SELECT *
	FROM dbix_class.person
	WHERE birth_year >= '1983'
) AS young_person

JOIN

dbix_class.person AS everyone

ON

young_person.person_id = everyone.person_id

	AND

young_person.person_id IN (
SELECT
	all_persons.person_id
FROM
	dbix_class.person AS person_id
	JOIN
	dbix_class.person AS person_name
	ON
	person_id.person_id = person_name.person_id
		AND
	EXISTS (
		SELECT person_id
		FROM dbix_class.person
		WHERE
			father_id = person_id.person_id
			OR
			mother_id = person_id.person_id
	)

	RIGHT JOIN

	dbix_class.person AS all_persons
	ON
	all_persons.person_id = person_id.person_id
WHERE
	person_name.name IS NULL
)
------------------------------------------

OK, my fantasy is over for now.

Ciao,
Emanuele.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: joins_test_db.sql
Type: application/octet-stream
Size: 2724 bytes
Desc: not available
Url : http://lists.rawmode.org/pipermail/dbix-class/attachments/20050806/72820746/joins_test_db.obj