How to return a null value from the table

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

Transact-SQL (2000)

How to return a null value from the table

Author

Topic

Chester
Starting Member

27 Posts

Posted - 2004-09-10 : 13:56:34

I'm using SQLserver2000.

My query looks like this:
select niin, field1, field2, ... etc
from table
where niin in ('aa','bb','cc'...etc)
My problem:
'cc' is not in the table but I want 'cc' to be returned in the recordset with NULL's in the rest of the columns like this --

niin field1 field2 ... etc
aa 1234 5678 ... etc
bb 123 456 ... etc
cc NULL NULL ... etc

Right now, cc does not appear in the output. How do I do this?

TIA

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-10 : 13:59:52

You may need to use a function to convert the csv to a table. You can find the function on this site.

SELECT table.niin, table.field1, table.field2
from dbo.CvsToTable(@niinItems) a LEFT OUTER JOIN table ON (table.niin = a.Item)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:06:36

select
niin, b.*
from
table
left outer join
(select nin, field1, field2, .... from table where niin in ('aa','bb','cc', ... )) b
on
table.niin = b.niin

assuming niin is the primary key. Without table structures or sample data, it is hard to know exactly what you are looking for.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-10 : 14:08:43

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99_Master (niin char(2) PRIMARY KEY, niin_desc varchar(255))
CREATE TABLE myTable99_Detail (niin char(2), Col1 int, Col2 int)
GO

INSERT INTO myTable99_Master (niin, niin_desc)
SELECT 'aa', 'aa description' UNION ALL
SELECT 'bb', 'bb description' UNION ALL
SELECT 'cc', 'cc description'

INSERT INTO myTable99_Detail (niin, Col1, Col2)
SELECT 'aa', 1,2 UNION ALL
SELECT 'aa', 1,3 UNION ALL
SELECT 'aa', 1,4 UNION ALL
SELECT 'aa', 2,1 UNION ALL
SELECT 'aa', 2,2 UNION ALL
SELECT 'bb', 1,2 UNION ALL
SELECT 'bb', 1,3 UNION ALL
SELECT 'bb', 1,4 UNION ALL
SELECT 'bb', 2,1 UNION ALL
SELECT 'bb', 2,2
GO

SELECT m.niin, d.Col1, d.Col2
FROM myTable99_Master m
LEFT JOIN myTable99_Detail d
ON m.niin = d.niin
GO

SET NOCOUNT OFF
DROP TABLE myTable99_Master
DROP TABLE myTable99_Detail
GO

[/code]

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:23:12

good point, Brett -- it probably is a Master/Detail kind of thing going on here; that the classic case of where these techniques come in handy.

Again, without tables we don't know for sure.

One thing you missed which is key on a LEFT join like that is the criteria (which he did indicate he would need) : it needs to be specified in the JOIN expression, or as a subquery within the join. if you just add it to the end as a WHERE , it will negate the LEFT OUTER join and make it an INNER join.

- Jeff

Chester
Starting Member

27 Posts

Posted - 2004-09-10 : 15:02:15

Thanks everyone.

Okay this is what I'm doing.

I have a web page where people will be typing in the niin's.
I have to return the niin's they typed in along with the info
from the table. The table has thousands of niin's but I only
need to return the ones that the user types in. The query that
Jeff provided returned all the niin's from the table and that
is not what the user wants to see. There will be multiple people
on this web page at the same time typing in their niin's. Is there
a way I can create a unique temp table for each user that I can
store the niin's they type in and run Brett's left join statement?

Again, many thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 15:37:33

Just create a permanent table with userId, niin, and entered.
Delete after some period of time (24 hours, 48 hours...)

Create Table userNiins
(
UserId int,
Niin varchar(100),
dateEntered datetime default(getdate())
)

Select *
From userNiins A
Left Join NiinTable B
on A.niin = B.niin
Where A.userId = @UserId

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources