通过sql中的特定数据从两个表中获取数据 [英] Fetch data from two table by specific data in sql

问题描述

表A

Table A

--------  
Mid   orgID
1       1	
2       2
3       2
4       4

表B

-------- -

Table B
---------

id Mid MemberID
 1  1   2
 2  1   3
 3  1   4
 4  2   1
 5  2   3
 6  2   4
 7  4   2
 8  4   3

In以上情况orgId和MemberID相同或视为相同。



如果我有orgID = 1.



我需要下表.....





表C

------ -

In above case orgId and MemberID is equivalent or treated as same.

If I have orgID=1.

I need following table.....


Table C
-------

Mid orgID
1    1
2    2

实际上我有orgID并希望搜索从两个表格中存在orgID存在的两个表格中获取所有Mid。意味着获得orgID存在的所有中间位置。



我已经提供了orgID = 1然后它应该按照我在表A和B中提到的数据获取以下数据/>




表C

-------

Actually I have orgID and want to search to get all Mid from both tables where orgID is presence
on both tables. Means get all mid where orgID is presence.

Let I have supplied orgID=1 then it should fetch following data as per I mentioned data on table A and B


Table C
-------

Mid orgID
1    1
2    2

注意：表A中存在OrgID并且发现mid = 1并且在表B中，MemberID等效于orgID，并且发现mid = 2。 Mid是表B中的外键引用。



我认为获得这种类型的输出是很多信息。



我用Google搜索并尝试了很多，但我没有得到。



请帮助我....

Note: In table A OrgID is present and found mid=1 And In table B MemberID is equivalent to orgID and found mid=2. Mid is foreign key reference in Table B.

I think it is much information to get that type of output.

I googled it and try it much myself but I didn''t get.

Please help me....

推荐答案

我不确定我是否理解你，但是......检查一下：

I''m not sure that i understand you well, but... check this:

DECLARE @tabA TABLE(Mid INT, orgID INT)

INSERT INTO @tabA (Mid, orgID)
VALUES(1, 1)
INSERT INTO @tabA (Mid, orgID)
VALUES(2, 2)
INSERT INTO @tabA (Mid, orgID)
VALUES(3, 2)
INSERT INTO @tabA (Mid, orgID)
VALUES(4, 4)

DECLARE @tabB TABLE(Mid INT, orgID INT, MemberID INT)

INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(1, 1, 2)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(2, 1, 3)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(3, 1, 4)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(4, 2, 1)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(5, 2, 3)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(6, 2, 4)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(7, 4, 2)
INSERT INTO @tabB (Mid, orgID, MemberID)
VALUES(8, 4, 3)

SELECT A.*
FROM @tabA AS A INNER JOIN @tabB AS B ON A.orgID = B.MemberID

关于加入的更多信息：

t-sql join [ ^ ]

SQL连接的可视化表示 [ ^ ]

DECLARE @tableA TABLE(Mid INT, orgID INT)

INSERT INTO @tableA (Mid, orgID)
VALUES(1, 1)
INSERT INTO @tableA (Mid, orgID)
VALUES(2, 2)
INSERT INTO @tableA (Mid, orgID)
VALUES(3, 2)
INSERT INTO @tableA (Mid, orgID)
VALUES(4, 4)

DECLARE @tableB TABLE(Mid INT, MemberID INT)

INSERT INTO @tableB (Mid, MemberID)
VALUES(1, 2)
INSERT INTO @tableB (Mid, MemberID)
VALUES(2, 3)
INSERT INTO @tableB (Mid, MemberID)
VALUES(3, 4)
INSERT INTO @tableB (Mid, MemberID)
VALUES(2, 1)
INSERT INTO @tableB (Mid, MemberID)
VALUES(5, 3)
INSERT INTO @tableB (Mid, MemberID)
VALUES(6, 4)
INSERT INTO @tableB (Mid, MemberID)
VALUES(7, 2)
INSERT INTO @tableB (Mid, MemberID)
VALUES(8, 3)

declare @A table
(
Mid int,
orgID int
)
declare @B table
(
Mid int,
orgID int
)
insert into @A select Mid,orgID from @tableA where orgID='1'
insert into @B select a.Mid,a.orgID
 from @tableA a, @tableB b where a.Mid=b.Mid and b.MemberID='1'
select * from @A union all select * from @B

这篇关于通过sql中的特定数据从两个表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！