mysql使用where子句连接2个表 [英] mysql join 2 tables with where clause

问题描述

我有两张桌子，比如



表1：FACULTY_DETAILS



fac_det_id（pk）.. ...... fname ...........供货情况

..... 1 ................. ........ xxx .................全职

..... 2 ........ ................. yyy .................兼职

..... 3 ......................... zzz .................工作日

..... 4 ......................... aaa ............... ..partime



表2：FACULTY



faculty_id（pk）....... ... course_id ........ fac_det_id（fk）

..... 1 .................... .......... 1 ...................... 2

..... 2 .. ............................ 2 ..................... .3

..... 3 .............................. 3 ... ................... 1

..... 4 ................ ................ 4 ...................... 3

.... 0.5 .............................. 3 ................. ..... 4



当我提供课程ID

i需要fname，可用性在table1和faculty_id在表2中



即如果我给course_id = 3然后我需要像



faculty_id .......... fname ...........资格

..... 2 ............ ....... xxx ................全职

..... 5 .......... ......... aaa ................ partime

i have two tables like

TABLE 1 : FACULTY_DETAILS

fac_det_id(pk)........fname...........availability
.....1......................... xxx.................full time
.....2......................... yyy.................part time
.....3......................... zzz.................weekdays
.....4......................... aaa.................partime

TABLE 2: FACULTY

faculty_id(pk)..........course_id........fac_det_id(fk)
.....1..............................1......................2
.....2..............................2......................3
.....3..............................3......................1
.....4..............................4......................3
.....5..............................3......................4

when i give course id
i need fname ,availability in table1 and faculty_id in table 2

ie if i give course_id=3 then i need like

faculty_id..........fname...........qualification
.....2...................xxx................full time
.....5...................aaa................partime

推荐答案

SELECT fname, availability, faculty_id
FROM faculty a, faculty_details b
WHERE a.fac_det_id = b.fac_det_id
AND course_id =1

自己解决了

solved myself

select  faculty_id,fname,availability
from table1 t1
JOIN table2 t2
ON t1.fdId=t2.fdId
where courseid =3

这篇关于mysql使用where子句连接2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！