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.

id text
1 house
2 kitchen
3 sink
4 bedroom

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 = parent) as pt,
       child,  (SELECT `text` FROM things WHERE = 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

From → Technology

Leave a Comment

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: