Joins

[morfasie]

Hi, is it possible to join threee tables in one join?

One table is people, other children and other assets?

I want a list of all the people with their child name and assets name?

So like this:

name | chilc name | assets name |


thanks

 

[PHV]

Any chance you could post some relevant info like, say, the tables schema and the relationships ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[morfasie]

No unfortundely not, but is it possible tojoin three tables?

thanks

 

[PHV]

Sorry, I quite don't understand your issue.
In a single SELECT instruction you may obviously join several tables, so where are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[morfasie]

Yes but I am using left join so as to get all the data from the first table and only those values where they have a pair in table 1 from the other tables. so normal select with 2 left joins?

But its not working correctly?

 

[PHV]

But its not working correctly
WHAT is not working ?
If you don't want to post infos, how do you expect that someone may help you ?
Personally, I'm not a mindreader and my crystal ball is in vacation today ...

 

[morfasie]

Ok here I am posting everything now

Productioncalc Table

CREATE TABLE `productioncalc` (
  `agent code` varchar(12) NOT NULL default '',
  `policy number` varchar(25) NOT NULL default '',
  `group name` varchar(200) NOT NULL default '',
  `branch` varchar(255) default NULL,
  `title` varchar(5) NOT NULL default '',
  `first name` varchar(15) default NULL,
  `surname` varchar(15) NOT NULL default '',
  `gender` varchar(7) default NULL,
  `ID` varchar(13) default NULL,
  `date of birth` varchar(10) NOT NULL default '',
  `telephone` varchar(10) NOT NULL default '',
  `cellphone` varchar(13) NOT NULL default '',
  `address` varchar(200) NOT NULL default '',
  `address code` varchar(5) NOT NULL default '',
  `basic plan premium` float(10,2) NOT NULL default '0.00',
  `additional child premium` float(10,2) NOT NULL default '0.00',
  `accidental benefit premium` float(10,2) NOT NULL default '0.00',
  `extended family premium` float(10,2) NOT NULL default '0.00',
  `bridging benefit premium` float(10,2) NOT NULL default '0.00',
  `collectionfee` int(4) NOT NULL default '0',
  `totalpremium` float(10,2) NOT NULL default '0.00',
  `benificiary name` varchar(200) default NULL,
  `benificiary surname` varchar(100) default NULL,
  `relationship` varchar(25) default NULL,
  `benificiary id` varchar(13) default NULL,
  `Individual` int(2) default '0',
  `name of bank` varchar(40) NOT NULL default '',
  `branch name` varchar(40) NOT NULL default '',
  `branch number` varchar(10) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `account type` varchar(20) NOT NULL default '',
  `account number` varchar(15) NOT NULL default '',
  `Cover Amount` varchar(6) NOT NULL default '',
  `inception date` date NOT NULL default '0000-00-00',
  `date captured` date NOT NULL default '0000-00-00',
  `CancellationDate` date default NULL,
  `EffectiveCancellationDate` date default NULL,
  `Reason` varchar(255) default NULL,
  `Paid` char(2) NOT NULL default '',
  `Cash Receipt Number` varchar(8) NOT NULL default '',
  `Children` char(2) NOT NULL default '0',
  `Club Fee` char(3) NOT NULL default '',
  `Club Fee Receipt` varchar(7) NOT NULL default '',
  `group code` varchar(8) default NULL,
  `paymentmethod` varchar(30) default NULL,
  `Plan_ID` varchar(20) default NULL,
  `waiting period` varchar(8) NOT NULL default '',
  `date removed` varchar(10) NOT NULL default '',
  `TransID` mediumint(9) NOT NULL auto_increment,
  PRIMARY KEY  (`TransID`),
  KEY `id_productioncalc` (`ID`),
  KEY `surname` (`surname`),
  KEY `policy number` (`policy number`),
  KEY `groupname` (`group name`)
) TYPE=InnoDB

second table

CREATE TABLE `dependants` (
  `MainTransId` varchar(15) NOT NULL default '0',
  `Main Member ID` varchar(13) NOT NULL default '',
  `Name` varchar(30) NOT NULL default '',
  `ID` varchar(13) default NULL,
  `date of birth` varchar(10) default NULL,
  `Age` char(3) default NULL,
  `Type` varchar(20) NOT NULL default '',
  `inception date` date NOT NULL default '0000-00-00',
  `TransID` mediumint(9) NOT NULL auto_increment,
  PRIMARY KEY  (`TransID`),
  KEY `maintransid` (`MainTransId`)
)

extendfamily
third table

CREATE TABLE `extendedfamily` (
  `MainTransId` varchar(15) NOT NULL default '',
  `Main Member ID` varchar(13) NOT NULL default '',
  `surname` varchar(25) NOT NULL default '',
  `firstname` varchar(25) NOT NULL default '',
  `dateofbirth` date NOT NULL default '0000-00-00',
  `age` char(3) NOT NULL default '',
  `ID` varchar(13) NOT NULL default '',
  `premium` double(4,2) NOT NULL default '0.00',
  `plan` varchar(15) NOT NULL default '',
  `subtotal` float(10,2) NOT NULL default '0.00',
  `inception date` date default NULL,
  `TransID` smallint(6) NOT NULL auto_increment,
  PRIMARY KEY  (`TransID`),
  KEY `maintranisd` (`MainTransId`)
)

I want to link a persons child with him and his extended people like aunt or brother etc.

the query is as follows so far :

select a.`group name`,a.`policynr` as 'Policy Nr',b.id,a.`inception date` as 'Inception Date','Funeral',left(a.planid,locate('.',a.planid,6)) as 'Plan Number',
	if(a.planid like '%SINGLE','SINGLE',if (a.planid like '%FAMILY','FAMILY',if (a.planid like '%SENIOR','SENIOR','SINGLE+CHILD'))) as 'Type',a.totalpremium as 'Premium',a.`cover amount`,b.`dateofbirth` as 'Main Member DOB',b.gender as 'Main Member Gender',c.`date of birth` as 'Dependant DOB',c.type as 'Type'
from  productioncalc a,groups d 

left join dependants c
on a.policyNr = c.maintransid

where b.transid = a.maintransid
and a.`group name` = d.`group name`
and d.`date deleted` is null
and a.cancellationdate is null

But when I put the third left join in it scrambles everything and doesn't give the correct data. It duplicates some of the members etc

 

[ddiamond]

Your query references table b in your where clause, but I don't see it in your from clause. Did you post an incomplete version of your query?

But when I put the third left join in it scrambles everything and doesn't give the correct data. It duplicates some of the members etc

Can you post your query with the 2nd left join which works, and then the query with the third left join which doesn't work?