CODEWITHSUNDEEP
sqlsql-server
JesseWolf
JesseWolf

Reputation: 62

Merge two rows with parent and child ID's

I've just started using SQL and I bumped into this problem. There must be a very easy solution I assume. All relations are saved in the relations table. Then there is a parent and a child. The parent being a company and the child being a contact of this company. Every relation has a RelID but the relation_relation table is used to split company and contact.

The problem I'm having is that I can't seem to get the company and the contact into one row like so: 

| nameCompany | nameContact |
-----------------------------
|random B.V.  | emmma       |
|random B.V.  | jason       |

I have two tables which I want to query. These are simplified versions with a few example values:

CREATE TABLE relations_relations (parentRelID INT, childRelID INT); 

INSERT INTO `relations_relations` VALUES (1, 1);
INSERT INTO `relations_relations` VALUES (1, 2);
INSERT INTO `relations_relations` VALUES (1, 3);

and

CREATE TABLE relations (RelID, nameContact, nameCompany); 

INSERT INTO `relations` VALUES (1, NULL, random B.V.);
INSERT INTO `relations` VALUES (2, emma, NULL);
INSERT INTO `relations` VALUES (3, jason, NULL);

Upvotes: 0

Views: 427

Answers (2)

JesseWolf
JesseWolf

Reputation: 62

i figured it out myself. you're supposed to join two time and use a different condition both times.

in this problem it should be:

Select *
from Test_Relations_Relations a
    inner join Test_relations b
        on a.childrelID = b.RelID
    inner join Test_relations c
        on a.parentrelID = b.relID

Upvotes: 0

Serge
Serge

Reputation: 4036

You need to JOIN the relation table to itself via the relations_relations table:

SELECT   p.nameCompany
        ,c.nameContact
FROM relations p
    INNER JOIN relations_relations rr
        ON p.RelID = rr.parentRelID
    INNER JOIN relations c
        ON c.RelID = rr.childRelID

Upvotes: 1

Related Questions