September 19, 2009 at 10:02 pm
hi all,
so i have a problem i cannot solve. i run a query that contains a "constant" (hardcoded) variable, and if the other variables from the db table come up blank, i still want a row returned (with null / blank for all db columns, but the hardcoded variables to show)
for example:
select name, address, '12345' as zip from mytable
where name='my_name'
if no entry exists in mytable where name='my_name', i get 0 rows.
but i want at least 1 row, showing (blank) for name, (blank) for address, and 12345 for zip.
i tried something like:
select isnull(name, ''), isnull(address, ''), '12345' as zip from mytable
where name='my_name'
but still 0 rows returned.
can someone please help?
September 20, 2009 at 2:59 am
untested, but it should work:
IF EXISTS(SELECT 1 FROM mytable WHERE name ='myname')
SELECT name, address, '12345' as zip from mytable WHERE name ='myname'
ELSE
SELECT 'name' AS name, 'address' AS address, '12345' as zip
September 20, 2009 at 3:01 am
September 20, 2009 at 3:02 am
September 20, 2009 at 11:30 pm
You could also put a UNION after your main query, with nulls for the database columns and the hard-coded constants, EG
select name, address, '12345' as zip from mytable
where name='my_name'
UNION ALL
select null, null,'12345'
This will always return a row with nulls and your hard-coded constants, even if your first query returned results. This may be something the app receiving your data has to cope with.
September 21, 2009 at 12:37 pm
You can build up a table (table variable) with your hard-coded values, parameters or whatever:
DROP TABLE MyTable
CREATE TABLE MyTable (
PersonId int,
FullName varchar(30),
Address1 varchar(50)
)
GO
INSERT MyTable (
PersonId,
FullName,
Address1
) VALUES (
123,
'John Smith',
'3325 Nowhere Ln'
)
-- Hard-coded values, passed-in parameters, whatever.
-- Edit the value of @Parm2 to see it work.
DECLARE
@Parm1 varchar(10),
@Parm2 int
SELECT
@Parm1 = '12345',
@Parm2 = 999 -- 123
-- However you got them, stuff them into a table
DECLARE @Values TABLE (
Parm1 varchar(10),
Parm2 int
)
INSERT @Values (
Parm1,
Parm2
) VALUES (
@Parm1,
@Parm2
)
SELECT
COALESCE(T.FullName, '') AS FullName,
COALESCE(T.Address1, '') AS Address1,
V.Parm1,
V.Parm2
FROM
@Values V
LEFT JOIN MyTable T
ON 1 = 1
AND T.PersonId = @Parm2
[whine]Except for the fact that the code tags really uglify my code, [/whine] this seems to meet the OP's requirements re: no rows returned without introducing any extra rows when data *is* found.
John Hopkins
September 22, 2009 at 2:23 pm
Adding to the post by christine.lawrie you can construct the UNION to only add the row if necessary:
SELECT [name], [address], '12345' AS zip
FROM mytable
WHERE [NAME] = 'my_name'
UNION ALL
SELECT TOP 1 NULL, NULL,'12345'
FROM mytable
WHERE (
SELECT COUNT(*)
FROM mytable
WHERE [name]='my_name'
) = 0
September 22, 2009 at 2:57 pm
thanks a ton everyone! here is what i ended up with:
select top 1 *
from
(
select name, address, zip from mytable where name='my_name'
union all
select '', '', '12345'
) t
order by name desc
very close to some of your replies - props to Andreas at sqlteam.com
i was so close, so many times - SQL WILL be my friend one day!
case closed!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply