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 (
PersonIdint,
FullNamevarchar(30),
Address1varchar(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
@Parm1varchar(10),
@Parm2int
SELECT
@Parm1 = '12345',
@Parm2 = 999 -- 123
-- However you got them, stuff them into a table
DECLARE @Values TABLE (
Parm1varchar(10),
Parm2int
)
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy