Return a record that doesn't exist in a table?

Unregistered · April 14th, 2006, 06:05 AM

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?

Jeff Mason · April 14th, 2006, 10:17 AM

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

Unregistered · April 17th, 2006, 02:59 AM

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?

Jeff Mason · April 17th, 2006, 08:52 AM

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

Unregistered · May 3rd, 2006, 01:27 AM

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.)