Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 14th, 2006, 06:05 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Return a record that doesn't exist in a table?

Hello. I'm new to this. I know this is a simple question, but I do not know how to even begin searching for it, so I'll take my chances and ask here just in case some kind soul will answer my question.

This is my query:

SELECT * FROM table
WHERE number in
(12345,
23456,
34567)

and my table looks like this:

number whatever something
---------------------------------
12345 data data
34567 data data

Now, I want the query to return a result that's similar to this:

number whatever something
---------------------------------
12345 data data
23456
34567 data data

I'm trying to get the query results to print '23456' even though '23456' does not exist in the table. Is this possible? Or am I trying to something fundamentally wrong here?


Reply With Quote
  #2 (permalink)  
Old April 14th, 2006, 10:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Where did the selection list of 'number' column values come from?

If it were in a database table, then an OUTER JOIN would give you what you want:
Code:
SELECT  * FROM <yourtable>
LEFT JOIN <tablewithselectionnumbers>
        ON <yourtable>.number = <tablewithselectionnumbers>.number
If the number value in <tablewithselectionnumbers> was not in <yourtable>, then the 'whatever', etc. columns in the resultset would be NULL.

If the selection values are not in a database table, then you'll have to create one.

You could place the values into a temporary (#...) table, or you could do this "on-the-fly" by creating a derived table:
Code:
SELECT  * FROM <yourtable>
LEFT JOIN 
        (SELECT 12345 as SelectionNumber
            UNION
         SELECT 23456
            UNION
         SELECT 34567) as Derived
    ON <yourtable>.number = Derived.SelectionNumber
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old April 17th, 2006, 02:59 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the answer, Jeff. I get what you're trying to say, although I still need some hand-holding through this.

This is my table again. (Let's call it NumberWithData)

number whatever something
---------------------------------
12345 data data
34567 data data


This is the list of 'number' I want to check in the table 'NumberWithData'.

12345
23456
34567
(and so on)


And this is the result I want.

number whatever something
---------------------------------
12345 data data
23456
34567 data data


The list contains over a thousand numbers to extract from the table, if it exists in the table.
If it doesn't (for example '23456'), it will print out the 'number' with NULL fields for 'whatever' and 'something'.

I don't want to imagine typing those thousands of numbers in by hand to create a temporary or derived table. Any easy way out?
Reply With Quote
  #4 (permalink)  
Old April 17th, 2006, 08:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Like I said in my original response:

"Where did the selection list of 'number' column values come from?"

If you have a list of " ... over a thousand numbers to extract from the table..." you don't expect to somehow construct an IN clause that has these thousands of entries in it, do you? How do you intend to do that?

Get it into the database and your query becomes simple. There are any number of ways to import that data into a database table, assuming you have your list of thousands of entries in some kind of rational machine-readable form.

Note that my use of the phrases " ...get it into the database ..." and "..import that data ..." doesn't necessarily mean that the list has to physically reside in a SQL Server database. You can construct what's called a "linked server" which actually refers to, say, an Excel spreadsheet which contains the list of 'numbers'. This link can then function as a database table and be referenced by queries such as the OUTER JOIN I suggested.

Search BOL for 'Excel' and 'linked server' to see how to do that.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old May 3rd, 2006, 01:27 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff, thanks for taking the time to help. I've figured out how to import a table from Access into the SQL Server database. Your examples were of great help, and will be of more help in the future. Thanks again.

(Sorry this reply is here bumping this thread. I looked and couldn't figure out if there was a way to PM people on this forum so I took to posting here.)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert where record doesn't already exist Neal SQL Server 2000 3 February 4th, 2008 10:09 AM
record exist or not keyvanjan Classic ASP Basics 2 May 5th, 2006 11:39 AM
Lock Fields if record already exist jimmy0305 Access VBA 2 February 2nd, 2006 03:07 PM
Record source ... does not exist tunsted Access 2 April 1st, 2004 10:03 AM
Return a Record set into a table? morpheus Classic ASP Basics 2 November 18th, 2003 11:38 AM



All times are GMT -4. The time now is 07:44 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.