CODEWITHSUNDEEP
mysql
Elshan
Elshan

Reputation: 7693

MySQL child data row has muliple parent keys and need to build select query

Parent Table(Animals)

enter image description here

Transaction Table

Sample Transaction

enter image description here

With Transaction table now I need to join parent table and needs to get the summary with Animal name like;

enter image description here

How I used select statement with joining Parent Table(Animals) And get above result ?

(previous developer create this structure and it's have almost 20GB data. Now I can't change the table structure)

Upvotes: 0

Views: 38

Answers (2)

tbedner
tbedner

Reputation: 323

I am riffing off of koryakinp's answer, but adding in a case statement to transform the value of the status:

SELECT 
   t.Transaction_ID,
   p.Name as 'Name of Father',
   m.Name as 'Name of Mother',
   c.Name as 'Name if Child',
   t.Date,
   CASE 
    WHEN t.Status = 1 THEN 'Active'
    WHEN t.Status = 2 THEN 'Value2'
    WHEN t.Status = 3 THEN 'Value3'
    WHEN t.Status = 4 THEN 'Value4'
    WHEN t.Status = 5 THEN 'Value5'
    ELSE t.Status
   END as Status
FROM transaction t
JOIN parent m ON m.ID = t.Animal_Mother_ID
JOIN parent p ON p.ID = t.Animal_Father_ID
JOIN parent c ON c.ID = t.Animal_Child_ID

and without the case statement:

SELECT 
   t.Transaction_ID,
   p.Name as 'Name of Father',
   m.Name as 'Name of Mother',
   c.Name as 'Name if Child',
   t.Date
FROM transaction t
JOIN parent m ON m.ID = t.Animal_Mother_ID
JOIN parent p ON p.ID = t.Animal_Father_ID
JOIN parent c ON c.ID = t.Animal_Child_ID

Upvotes: 1

koryakinp
koryakinp

Reputation: 4125

SELECT TOP 1
   t.ID,
   p.Name as 'Name of Father',
   m.Name as 'Name of Mother',
   c.Name as 'Name if Child',
   t.Date,
   t.Status
FROM Transactions t
JOIN Animals m ON m.ID = t.Animal_Mother_ID
JOIN Animals p ON p.ID = t.Animal_Father_ID
JOIN Animals c ON c.ID = t.Animal_Child_ID

Upvotes: 2

Related Questions