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

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

SQL Retrieve data from multiple tables

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

SQL Retrieve data from multiple tables

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us