Skip to content

Database dereferencing double foreign key

February 22, 2013

There exists a relationship between database tables where a table has two foreign keys into the same table. Here is a simple example.

things
id text
1 house
2 kitchen
3 sink
4 bedroom

Relation
id parent child
1 1 2
2 1 4
3 2 3

The columns parent and child are both foreign keys into things.

SELECT parent, (SELECT `text` FROM things WHERE things.id = parent) as pt,
       child,  (SELECT `text` FROM things WHERE things.id = child)  as ct
       FROM relation;

Will display the index numbers along with the associated text.

parent pt child ct
1 house 2 kitchen
1 house 4 bedroom
2 kitchen 3 sink
Advertisements

From → Technology

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: