SQL Retrieve data from multiple tables

Question

1.00/5 (2 votes)

See more:

I have 2 tables, I want to retrieve the data from both tables with a single stored procedure.

The 2nd table has multiple rows that link to a single row in the 1st table.

Table 1

Field 1
Field 2
Field 3

Table 2

Field 1_1
Field 2_1
Field 3_2
Field 4_2
Field 5_3
Field 6_3

The tables are linked via a PK and FK

Field 1_1, refers to the 2nd tables field (Field 1) linking to the 1st tables field (_1)

My question is ... I would like to view data from both tables from a single stored procedure, when i execute the stored procedure the result should be as follows:

Field 1 - Field 1_1
Field 1 - Field 2_1
Field 2 - Field 3_2
Field 2 - Field 4_2
etc...

What i have tried so far...

SQL

Create Proc usp_Test
As
Select t1.Field1, t1.Field2, t1.Field3, t2.Field1, t2.Field2, t2.Field3, etc
From tbl_Table1 t1 right outer join tbl_Table2 t2 on t1.PK = t2.FK

Please help...

Posted 15-Sep-13 1:10am

D3m0n1CMoNkEy

Updated 15-Sep-13 6:01am

v6

Add a Solution

Comments

[no name] 15-Sep-13 7:11am

Okay and? Did you have a question? Maybe you have some sort of a problem with your SP?

D3m0n1CMoNkEy 15-Sep-13 7:15am

I updated my question

CHill60 15-Sep-13 11:32am

Have you tried writing any sql to do this?

D3m0n1CMoNkEy 15-Sep-13 11:38am

Yes, i thought using a right outer join might be the solution, but im still unsure. I dont know if its the right thing to use or if there is an easier solution

CHill60 15-Sep-13 11:55am

I probably wouldn't have done it that way, but it's hard for any of us to comment if you don't post what you have already tried

D3m0n1CMoNkEy 15-Sep-13 12:03pm

Updated the question with an example

CHill60 15-Sep-13 12:11pm

That looks fine to me! I personally would use a left outer join - but I can be a little picky over things like that :-) This link provides a decent explanation of the difference http://www.programmerinterview.com/index.php/database-sql/difference-between-a-left-outer-join-and-right-outer-join/[^]

You might also want to add an Order By clause to make sure the data comes out as per your expected results

D3m0n1CMoNkEy 15-Sep-13 12:17pm

i thought the right outer join would allow for multiple rows from table 2 that link to the single row from table 1, and vice versa for left outer join. Why do you think the left join is better than the right?

CHill60 15-Sep-13 12:41pm

both will essentially "merge" the tables (for want of a better phrase). I just personally prefer the left join because in my culture we read from left to right so it feels more natural. Sorry - I didn't mean to imply that it was technically better in this case.

Zerotimedev 17-Sep-13 7:57am

I'm the same I tend to always use a left outer join and work left to right. :)

2 solutions

Solution 1

I think that can be achieved by using simple INNER JOIN

SQL

SELECT
TABLE1_FIELD, TABLE3_FIELD
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1_FIELD = TABLE2_FIELD

Posted 17-Sep-13 22:39pm

Gauri Chodanker

Comments

CHill60 18-Sep-13 11:56am

That's fine, but if there is a row on TABLE1 that does not have a corresponding row or set of rows on TABLE2 then it will be excluded from the results. Using a LEFT OUTER join would return everything from TABLE1

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)

D3m0n1CMoNkEy · Accepted Answer · 2013-09-20T00:20:00

Solution 2

SQL

Create Proc usp_Test
As
Select t1.Field1, t1.Field2, t1.Field3, t2.Field1, t2.Field2, t2.Field3, etc
From tbl_Table1 t1 left outer join tbl_Table2 t2 on t1.PK = t2.FK
Order By t1.Field1

Posted 20-Sep-13 0:20am

D3m0n1CMoNkEy