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

Join question

Author

Topic

mauich123
Starting Member

21 Posts

Posted - 2011-01-27 : 10:38:12

I have two tables, with different structures although some similar fields. I want to select everything from both tables and present them in a datagridview. If the ordernumber field is the same I want that row to contain every field from both tables otherwise only for table1 or table2 and the remaining columns should be empty. I'm a bit confused here. In my DataGridView I first get all entries from Table1 with the table2-columns empty and then vice versa after all these rows. What am I doing wrong?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 10:43:49

select *
from t1
full outer join
t2
on t1.num = t2.num

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mauich123
Starting Member

21 Posts

Posted - 2011-01-27 : 10:44:52

quote:
Originally posted by nigelrivett

select *
from t1
full outer join
t2
on t1.num = t2.num

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

This is how I'm doing it actually

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 10:49:50

Then your join criteria is not getting any matches.
Check the data in the tables.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mauich123
Starting Member

21 Posts

Posted - 2011-01-28 : 03:00:58

The problem is that my orderfield automatically gets two fields in the datatableadapter. order and order1. I want both to be "merged" into order so to speak.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 03:16:11

post some sample record and show the expected result.

KH
[spoiler]Time is always against us[/spoiler]

mauich123
Starting Member

21 Posts

Posted - 2011-01-28 : 03:34:31

Here's the basic outlining. This is what i get from a full outer join with ON table1.order=table2.order. There's two problems:
1) I don't want two order columns, just the one... either if they're the same or if it's only present in one of the tables.
2) Every time there's an ordernumber present in table2 there ALWAYS a entry with the same ordernumber in table1 (but not vice versa). Although this doesn't show up in my DataGridView, it displays it as it's only present in table2

Note that order1 = order for table2 (Visual Studio renamed it to order1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 03:40:42

use ISNULL() or COALESCE()


select order = isnull(t1.order, t2.order),
       t1.date, t2.date,
       t1.amount, t2.amount
from   table1 t1
       full outer join table2 t2 on t1.order = t2.order

KH
[spoiler]Time is always against us[/spoiler]

mauich123
Starting Member

21 Posts

Posted - 2011-01-28 : 05:38:04

quote:
Originally posted by khtan

use ISNULL() or COALESCE()

select order = isnull(t1.order, t2.order),
t1.date, t2.date,
t1.amount, t2.amount
from table1 t1
full outer join table2 t2 on t1.order = t2.order

KH
[spoiler]Time is always against us[/spoiler]

Great, thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources