Fetch data from two table by specific data in sql

Question

1.00/5 (4 votes)

See more:

Table A

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

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 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

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

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....

Posted 14-May-13 4:50am

rajesh@1989

Updated 14-May-13 9:13am

v5

Add a Solution

Comments

CHill60 14-May-13 10:58am

So what have you tried so far?

Michael J. Eber 14-May-13 11:01am

Is there any kind of question in this? Do you think we will do your homework for you?

Maciej Los 14-May-13 11:26am

Based on which criteria do you want to fetch data:

Mid orgID
1    1
2    2

?
I don't see any dependencies...

Please, be more specific and provide more details. Use "Improve question" to update your question.

2 solutions

Solution 1

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

SQL

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

More about joins:
t-sql join[^]
Visual Representation of SQL Joins[^]

Posted 14-May-13 9:16am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

rajesh@1989 · Accepted Answer · 2013-05-19T23:34:00

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