1

So I have a generalised table called beneficiaries which gives the specialised table parent and child. So parent and child table reference a beneficiary id. However, child table references a parent beneficiary id. Now my struggle is I want to write a query which returns the name of the child from the beneficiary and the name of the parent from the beneficiary while showing which parent a child belongs to. I wrote this query:

select * from beneficiaries
inner join child on beneficiaries.bene_id = child.ParentBene_id
inner join parent on beneficiaries.bene_id = parent.parentBene_id;

But the results I get is just the parent name and the id for the child. Structure of Tables

Beneficiaries table

Child Table

Parent Table

Mozart of Computer
  • 11
  • 1

2 Answers2

0

Your query looks great. I created some tables with sample data and this is my result.

enter image description here

Maybe you forgot to declare the foreigns keys when creating the tables? Here is the commands I used to create your tables in MySQL:

  • Beneficiaries table:
CREATE TABLE `beneficiaries` (
  `bene_id` int(11) NOT NULL,
  `kayacare_id` int(11) DEFAULT NULL,
  `fname` tinytext,
  `mname` tinytext,
  `lname` tinytext,
  `location` tinytext,
  `dob` tinytext,
  `sex` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`bene_id`)
)
  • Child table:
CREATE TABLE `child` (
  `bene_id` int(11) DEFAULT NULL,
  `parentBene_id` int(11) DEFAULT NULL,
  `healthWorker_id` int(11) DEFAULT NULL,
  `careGiver_id` int(11) DEFAULT NULL,
  KEY `parentBene_id` (`parentBene_id`),
  FOREIGN KEY (parentBene_id) REFERENCES beneficiaries(bene_id)
  )
  • Parents table:
CREATE TABLE `parent` (
  `parentBene_id` int(11) DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  KEY `parentBene_id` (`parentBene_id`),
  FOREIGN KEY (parentBene_id) REFERENCES beneficiaries(bene_id)
)

Here you can see how to edit a foreign key in MySQL.

lassault
  • 48
  • 1
  • 5
  • Yes, so the thing is I want the parent name, to show in the query results as well so that we can identify a child and a parent together – Mozart of Computer Dec 12 '20 at 16:13
  • If you only want the names or to have it as your first values in the result, you can ask then like this: `select fname, lname, mname from beneficiaries inner join child on beneficiaries.bene_id = child.ParentBene_id inner join parent on beneficiaries.bene_id = parent.parentBene_id;` I assume that is the parent name, if you are looking for child names, you don't have any field like that in your table. – lassault Dec 12 '20 at 16:22
0

To get the results I wanted I used this query:

select * from child
right join beneficiaries on child.bene_id = beneficiaries.bene_id  
inner join (select parentBene_id,fname as pfname, mname as pmname, lname as plname, phone from 
parent inner join beneficiaries on parent.parentBene_id = beneficiaries.bene_id) 
parent on child.parentBene_id = parent.parentBene_id;
Mozart of Computer
  • 11
  • 1