如何使用DISTINCT和JOIN从表中获取信息？ [英] How can I use DISTINCT and JOIN to get information from to tables?

问题描述

I have a two tables A and B

In table A I have two coloumns date and ID. I would like to get IDs from table A for all dates except two dates "20140115" and "20140425". 
Some ID from table A can appear multiple times, therefore I have to use distinct.
After I get all IDs from table A, then I have to use the extract IDs from table A to get the data from table B.Because the same IDs exist in both tables.
I know that I have to use both distinct and join. 

I need help with combination of both distinct and join to get what I have explained above.
I need something like this. 


SELECT A.ID, B.ID
FROM (select distinct ID from A where date<>20140115 and date<>20140125) AS A
JOIN (select ID) AS B
ON A.ID = B.ID;

Thanks

推荐答案

如果我理解正确，你想从表A得到一份不同的ID列表然后从表B中检索包含ID的记录。



在这种情况下，您可以尝试使用in语句。

例如

If I understand you correctly you want to get a distinct list of ID from Table A and then retrieve the records from Table B that contain the IDs.

In this case you can try the in statement.
eg

Select * from tableB where ID in (select distinct ID from TableA where date not in ('15-Jan-2015','25-Jan-2015'))

这篇关于如何使用DISTINCT和JOIN从表中获取信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！