Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

MSSQL join question


  • Please log in to reply
2 replies to this topic

#1 FayB

FayB

  • Members
  • 46 posts
  • OFFLINE
  •  
  • Gender:Not Telling
  • Local time:02:39 AM

Posted 04 March 2011 - 11:24 PM

I know some basic sql but have not done anything too complicated.
When I first learned SQL - I did not use JOIN to joine table s but did something like where a.thejoinfield = b.thejoinfield
It only returned rows that were in both tables.

I have a projecct at where I need to write some SQL that will return all rows from both tables.
This is not the acutal files but an example of what I want to do.

the common field that the table will be joined on is: thename
table1 has fields thename, grosspay, netpay
table1 has fields thename, address, phone

I need to join the tables and return thename, grosspay, netpay, address, phone
I think I want to use a full join.

I'll be joining the talbe on thename,
I think I can do some looking and figure out how to handle the other fields when they are nulls, but don't know how to handle returning the join field.
If I was always returning entries from table1 (alias a) I would : select a.thename
But I don't know if I'll be returning a row from table1 or table2 so how do I select back thename field

Thanks,
Fay

BC AdBot (Login to Remove)

 


#2 Didier Stevens

Didier Stevens

  • BC Advisor
  • 2,707 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:09:39 AM

Posted 05 March 2011 - 07:45 AM

With MSSQL you mean Microsoft SQL Sever, thus the T-SQL language?

An outer join of these 2 tables (listing all entries, including keys unique to one table) is:

select table1.thename, table2.thename, grosspay, netpay, address, phone from table1
full outer join table2 on table1.thename = table2.thename

But some of the entries in the first or second column will be null.

To avoid this, you can use the isnull function:

select isnull(table1.thename, table2.thename), grosspay, netpay, address, phone from table1
full outer join table2 on table1.thename = table2.thename

Didier Stevens
http://blog.DidierStevens.com
http://DidierStevensLabs.com

SANS ISC Senior Handler
Microsoft MVP 2011-2016 Consumer Security, Windows Insider MVP 2016-2019
MVP_Horizontal_BlueOnly.png

 

If you send me messages, per Bleeping Computer's Forum policy, I will not engage in a conversation, but try to answer your question in the relevant forum post. If you don't want this, don't send me messages.

 

Stevens' law: "As an online security discussion grows longer, the probability of a reference to BadUSB approaches 1.0"


#3 FayB

FayB
  • Topic Starter

  • Members
  • 46 posts
  • OFFLINE
  •  
  • Gender:Not Telling
  • Local time:02:39 AM

Posted 05 March 2011 - 10:05 AM

Didier,

Geeez I should have though of that. That looks exaclty like what I need.
THANKS.

Fay




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users