CODEWITHSUNDEEP
sqldatabaseselect
Renuka Chauhan
Renuka Chauhan

Reputation: 13

Sql data from row to column with reference to another column

Parent table

+====+===========+
| id | firstname |
+====+===========+
|  1 | abc       |
+----+-----------+
|  2 | bcd       |
+----+-----------+
|  3 | cde       |
+----+-----------+

StudentRelationship table

+==========+==========+===========+
| relation | parentid | studentid |
+==========+==========+===========+
| father   |        1 | s0001     |
+----------+----------+-----------+
| mother   |        2 | s0001     |
+----------+----------+-----------+
| father   |        3 | s0002     |
+----------+----------+-----------+

STUDENT table

+=======+===========+==========+=========+======+
|  id   | firstname | lastname | address | sex  |
+=======+===========+==========+=========+======+
| s0001 | shdj      | khb      | jxx     | male |
+-------+-----------+----------+---------+------+

It would be great if you could help me create a query which will return studentid ,name,father name,mother name,sex,address.

Upvotes: 1

Views: 98

Answers (1)

Brien Foss
Brien Foss

Reputation: 3367

Based on what you've posted, then updated in your comments, I think this should work for you. I am sure someone with more advanced SQL skills can post a more elegant way to do this. But this is what I came up with:

SELECT DISTINCT cte.studentid
    ,studentFirstName
    ,studentLastName
    ,father.fatherFirstName
    ,mother.motherFirstName
    ,sex
    ,address
FROM cte
LEFT JOIN father ON cte.studentid = father.studentid
LEFT JOIN mother ON cte.studentid = mother.studentid

The following is an example where a student (Jeff Jones) has two fathers (let's say one of them is the step-father):

example

A few recommendations here:

  1. Take a course on SQL syntax fundamentals (any type MySQL, T-SQL, etc..)
  2. Read about FROM and JOIN
  3. When posting your question here, the table examples should have better test data. "asdfkj", "shdsf", "Asdjkfdjkf" are horribly hard to use to test code against because there is no context of what you are looking at. I realize you are just posting an example, and the context of the rows is partly insignificant, but it just makes for easier question answering, and doesn't scare off people who would want to answer your question.

Here is an DEMO you can play with, that has reasonable data in the fields you've mentioned.

Upvotes: 1

Related Questions