joining 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

SQL Server 2000 Forums

SQL Server Development (2000)

joining tables

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-03-19 : 11:47:20

I have the following tables with some sample data in them.

Account
AcctNumber AcctName
1111 Joe User
1234 Jim Brown

ReportUsers
lngIndex AcctNumber AcctName UserId Password
1 1111 Joe User joeuser password
2 2222 Sys Admin sysadmin password

I'd like to pull all the records and fields from both tables. Notice that Joe User exists in both tables, but Jim Brown and Sys Admin exist only in account and reportusers respectively.

I've thought of using union, but all the expressions in my select clause should have the same number. Outer join would only give me the record that exists only in one table (e.g., Jim Brown), but not Sys Admin.

Pls help. Thank you.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-03-19 : 12:06:28

what is your expected results


1    1111 Joe User  joeuser  password
2    2222 Sys Admin sysadmin password
Null 1111 Joe User  null     null
Null 1235 Jim Brown null     null


1    1111 Joe User  joeuser  password
2    2222 Sys Admin sysadmin password
Null 1235 Jim Brown null     null

Got SQL?

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-03-19 : 12:19:41

This is what I expect to see from the recordset.
1 1111 Joe User joeuser password
2 2222 Sys Admin sysadmin password
Null 1235 Jim Brown null null

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-03-19 : 12:43:06

try this:


SELECT ru.IngIndex, 
	Case 
		WHEN ru.Acctnumber IS NULL THEN a.Acctnumber 
		ELSE ru.AcctNumber 
	END,
	Case 
		WHEN ru.AcctName IS NULL THEN a.AcctName
		ELSE ru.AcctName
	END,
	ru.UserID,
	ru.Password
FROM ReportUsers ru 
	FULL OUTER JOIN Account a
		ON (ru.AcctNumber = a.AcctNumber)

SELECT * FROM ReportUsers ru 
	FULL OUTER JOIN Account a
		ON (ru.AcctNumber = a.AcctNumber)

if you expect to have duplicate AcctNumbers then you will need to group these results.

Got SQL?

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-03-19 : 13:41:06

Thank you. It worked.

PeterG
Posting Yak Master

156 Posts

Posted - 2003-03-19 : 15:55:30

Here's my stored proc:
SELECT AccountName = Case
WHEN ru.Name IS NULL THEN a.Name
ELSE ru.Name
END,
CAST(ru.lngIndex as varchar(10)),
Case
WHEN a.AccountNumber IS NULL THEN ru.AccountNumber
ELSE a.AccountNumber
END,
ru.UserID + '`' +
ru.Password + '`' +
CAST(ru.Manager as varchar(10)) + '`' +
CAST(ru.IsManager as varchar(5)) + '`' +
CAST(ru.HasAccount as varchar(5)) as UserDetails
FROM ReportUsers ru
FULL OUTER JOIN Cardholder a
ON (ru.AccountNumber = a.AccountNumber)
ORDER BY AccountName

This gives me:
Joe User 1 1234567890 joeuser`password`1`0`0
MEYBOT, PIET NULL 4715360000261557 NULL

where I have the name, index, acctnumber, userdetails field values are shown. Note that Joe User is from the reportusers table while Meybot is from the cardholder table. When I attached the index and the acctnumber fields to the userdetails, this is what I get:

User, Joe 1`1234567890`joeuser`password`1`0`0
MEYBOT, PIET NULL

What happened to MEYBOT's account number (4715360000261557)? It's showing as NULL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources