Source data from two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Source data from two tables

Author

Topic

ishchopra
Starting Member

24 Posts

Posted - 2011-10-19 : 07:32:49

Hello Experts,

Here is my problem:

I have two tables which have identical columns but different data. I am just wondering if i can bring in all the records from Table 1 adjacent to Table 2.

For Example:

Table 1 have 3 records
Table 2 have 5 records

I need output like this

Table 1 ..............Table 2

ABC.....................DEF
DEF.....................JKL
GEH.....................ABC
Null....................GEH
Null....................XYZ

Please let me know if that is achievable

I need it for comparison but all the records not just common

Regards,

Ish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 07:37:45

sounds like what you need is full outer join between tables on common fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ishchopra
Starting Member

24 Posts

Posted - 2011-10-19 : 07:44:25

Can you please explain more on this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 08:18:05

can you please provide sample data and explain what output you want so that i can give query accordingly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ishchopra
Starting Member

24 Posts

Posted - 2011-10-19 : 09:05:56

Hey apologize for late reply,

Here you go

Table Names

Win41
Win42

Name of Fields i need from both tables:

ID
Margin

And mind it i am trying to do this in Access as well as sql.

Please let me know if you need anything else

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 09:35:39

[code]
SELECT t1.Margin,t2.Margin
FROM table1 t1
FULL OUTER JOIN Table2 t2
On t2.id = t1.id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ishchopra
Starting Member

24 Posts

Posted - 2011-10-19 : 10:09:23

FULL OUTER JOIN doesnt work in Access.. do you have any alternative ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 10:12:12

if you're looking at Access query, please post in access forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmaster555112
Starting Member

13 Posts

Posted - 2011-10-19 : 10:59:42

Hi Ish,
I think this should work,

SELECT t1.id,t1.Margin,t2.Margin
FROM table1 t1
inner JOIN Table2 t2
On t2.id = t1.id

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:30:50

quote:
Originally posted by sqlmaster555112

Hi Ish,
I think this should work,

SELECT t1.id,t1.Margin,t2.Margin
FROM table1 t1
inner JOIN Table2 t2
On t2.id = t1.id

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

it wont as per OPs output
it wont fetch unmatching records at all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmaster555112
Starting Member

13 Posts

Posted - 2011-10-20 : 10:36:07

The answer posted on that link was for the values where its not written null coz as far as I remember previously Null was not written in the Question.

here is the solution to the problem with null stuff...........

Suppose we have two tables data and table2 each with 2 columns of the form
Data Table2
id Value id Valuesss
1 10 1 54
2 20 2 78
3 8 3 213
4 87 4 254
5 51 5 95
6 65
7 56
8 66

then write this query

SELECT *
FROM data INNER JOIN table2 ON data.ID=table2.ID
Union
SELECT *
FROM data LEFT JOIN table2 ON data.ID = table2.ID
WHERE (table2.id) is null
Union
SELECT *
FROM data RIGHT JOIN table2 ON data.ID = table2.ID
WHERE (data.id) is null;

it is a substitute for Full outer Join in SQL server
Try this it has given the right answer to me.

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:46:35

quote:
Originally posted by sqlmaster555112

The answer posted on that link was for the values where its not written null coz as far as I remember previously Null was not written in the Question.

here is the solution to the problem with null stuff...........

Suppose we have two tables data and table2 each with 2 columns of the form
Data Table2
id Value id Valuesss
1 10 1 54
2 20 2 78
3 8 3 213
4 87 4 254
5 51 5 95
6 65
7 56
8 66

then write this query

SELECT *
FROM data INNER JOIN table2 ON data.ID=table2.ID
Union
SELECT *
FROM data LEFT JOIN table2 ON data.ID = table2.ID
WHERE (table2.id) is null
Union
SELECT *
FROM data RIGHT JOIN table2 ON data.ID = table2.ID
WHERE (data.id) is null;

it is a substitute for Full outer Join in SQL server
Try this it has given the right answer to me.

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

thats why i suggested full outer join in my first suggestions itself but its no available in acces and hence has to be simulated as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources