CODEWITHSUNDEEP
mysqlsqljoinleft-join
Techie
Techie

Reputation: 45124

Join tables according to a column of the table

I have a table which stores the important dates.

enter image description here

importantDateType column can be either 1 or 2. Given that this is a dropdown in the front-end.

I have separate two tables to store Electorate Division & Village details. What I do is store in importantDateParent is the ID of the either Electorate Division or Village.

My problem is if the a particular record's importantDateType is 1, important dates table should join with Electorate Division table and get the name of the Electorate Division. if the a particular record's importantDateType is 2, important dates table should join with Village table and get the name of the Village.

What I'm using right now is 

SELECT `tbl_importantdates`.*,
       `tbl_elecdivision`.`elecDivName`,
       `tbl_villages`.`villageName`
FROM (`tbl_importantdates`)
LEFT JOIN `tbl_elecdivision` ON `tbl_importantdates`.`importantDateParent` = `tbl_elecdivision`.`elecDivID`
LEFT JOIN `tbl_villages` ON `tbl_importantdates`.`importantDateParent` = `tbl_villages`.`villageID`
WHERE `tbl_importantdates`.`status` = '1'

I don't want to have two columns like tbl_elecdivision.elecDivName and tbl_villages.villageName. How can I have column called importantDateTypeName which contains the name of the Electorate Division or Village depending on importantDateType being 1 or 2 ?

Upvotes: 0

Views: 79

Answers (1)

aviad m
aviad m

Reputation: 186

did you try using a case ... when ... in your select clause

you should something like this:

select `tbl_importantdates`.*,
case when importantdatetype = 1 then tbl_elecdivision.elecDivName,
case when importantdatetype = 2 then tbl_villages. villageName
else 'nothing' end name
--.......rest of code your comes here

Upvotes: 1

Related Questions