MySQL join columns of multiple tables

Question

I have 3 tables. How to display cardNumber and coresponding group based on this situation. I can then create a view and see the card number and which group it belong to.

 **1.ClientCards:**
 1.1cardID
 1.2cardNumber
 1.2relCardTypeID - foreign key of 2.1cardTypeID

 **2.cardTypes:**
 2.1cardTypeID - foreign key of 3.1groupID
 2.2relParrentID
 2.2cardTypeName

 **3.cardGroups:**
 3.1groupID
 3.1groupName

There are many questions related to it in StackOverflow.. better search first if it's not available then post a question here. — Pradeep Kumar, Mar 03 '17 at 10:25

Pradeep Kumar · Accepted Answer · 2017-03-03T10:34:58.923

0

Hope your mapped wrong as relParrentID should be foreign key of 3.1 groupID

Try this:

SELECT cc.cardNumber, cg.groupName
FROM ClientCards cc
INNER JOIN cardTypes ct ON cc.relCardTypeID = ct.cardTypeID
INNER JOIN cardgroups cg ON ct.relParrentID = cg.groupID

edited Mar 03 '17 at 10:34

answered Mar 03 '17 at 10:32

Pradeep Kumar

4,065
2
33
40

yes it is i mapped wrong – citizen Mar 03 '17 at 10:57
hope this query worked for you – Pradeep Kumar Mar 03 '17 at 10:59
yes it works why use inner join and not left join ? – citizen Mar 03 '17 at 10:59
left join better to use. inner join ignores the data where we don't have result in joined tables. http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins – Pradeep Kumar Mar 03 '17 at 11:01

score 0 · Answer 2 · answered Mar 03 '17 at 11:01

0

VIEW `card_groups` AS
SELECT 
    `c`.`cardNumber` AS `cardNumber`,
    `g`.`groupName` AS `groupName`,
    `t`.`cardTypeName` AS `cardTypeName`
FROM
    ((`s2_1_cards` `c`
    LEFT JOIN `fxml4_4_card_types` `t` ON ((`c`.`relCardTypeID` = `t`.`cardTypeID`)))
    LEFT JOIN `fxml4_3_groups` `g` ON ((`g`.`groupID` = `t`.`relParrentID`)))

answered Mar 03 '17 at 11:01

citizen

53
1
6

code looks like this: – citizen Mar 03 '17 at 11:02

MySQL join columns of multiple tables

2 Answers2