Using subqueries with multiple results which depend on the main query

Question

I have a persons table called "tc_person" and a marriage table called "tc_marriage". I want to select a few columns from my persons table and one column which represents the id of the partner.

The marriage table includes the pid_1 and pid_2 of two people - but it is important that there is only one entry for a couple and the order of the couples ids may vary. Here's the tables:

tc_person:

|   id    |    name    |   lastname  |
--------------------------------------
|    4    |    peter   |    smith    |
|    5    |    sarah   |    smith    |

tc_marriage:

|   id    |    pid_1   |    pid_2    |
--------------------------------------
|    0    |      5     |      4      |
|    1    |      7     |      9      |

It seems that my subquery is interpreted as a whole before the original select statement. Now I get the error that my subquery returns more than one row.

SELECT p.id, p.name, p.lastname,

(SELECT m.pid_1 FROM tc_marriage m WHERE m.pid_2 = p.id UNION
 SELECT m.pid_2 FROM tc_marriage m WHERE m.pid_1 = p.id) as partner_id

FROM tc_person p WHERE p.lastname LIKE 'smith';

I am looking for the following output:

|   id    |    name    |   lastname  |  partner_id  |
-----------------------------------------------------
|    4    |    peter   |    smith    |       5      |
|    5    |    sarah   |    smith    |       4      |

Is this even possible with only one single query? You can probably tell by now that I'm quite the SQL noob. Maybe you guys can help.

PinnyM · Accepted Answer · 2012-12-19 16:57:47Z

1

You can use IN to avoid a UNION (which is typically slower), and a CASE statement to pick out the correct partner_id:

SELECT p.id, 
       p.name, 
       p.last_name, 
       CASE p.id WHEN m.pid_1 THEN m.pid_2 ELSE m.pid_1 END AS partner_id
FROM tc_person p
JOIN tc_marriage m ON p.id IN (m.pid_1, m.pid_2)

answered Dec 19, 2012 at 16:57

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2012-12-19 16:58:42Z

0

What you want to do is to join the person table to the marriage table. However, the problem is that you have two keys. One solution is to do two joins and then some logic to choose the right value.

I prefer to "double" the marriage table by swapping the partners. The following query takes this approach in a subquery:

select p.id, p.name, p.lastname, partner_id
from tc_person p join
     (select pid_1 as firstid, pid_2 as partner_id
      from marriage
      union all
      select pid_2 as firstid, pid_1 as partner_id
      from marriage
     ) m
     on p.id = m.firstid

answered Dec 19, 2012 at 16:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Using subqueries with multiple results which depend on the main query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related