dongnao6858 2017-11-17 12:01
浏览 50
已采纳

简单MySQL两个表查询[重复]

This question already has an answer here:

I have two tables

1st table :-

id name dept
1  John dept1
2  Mary dept2
3  Dave dept3
4  John dept4
5  John dept5


2nd table :-

id submitter dept
1  Rupert    dept3
2  Joe       dept1
3  Lisa      dept2
4  Louise    dept4
5  Tom       dept5

what i would like is a query to allow people in the name column in the first table to only show records based on their matching departments eg John in table one will return the 3 records in table 2 (id 2,4 and 5)

So far i have tried SELECT * FROM table1, table2 WHERE table1.dept = table2.dept AND table1.name='John'

</div>
  • 写回答

1条回答 默认 最新

  • duandaodao6951 2017-11-17 12:07
    关注

    If I correctly understand your problem, you need to make a join between the two tables using the field dept and filter your results by the name of the requester, in the first table.

    SELECT t2.submitter, t2.dept
    FROM table1 t1
    LEFT JOIN table2 t2 ON t1.dept = t2.dept
    WHERE t1.name = :person_name
    

    Documentation and examples (you can also look at the left menu at Inner, right, full and self join's).

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?