Return multiple columns with IIF?

M

Martin

Hi
I'm trying to return a record that another referres to and have come to the follwing sql query
SELECT TOP
IIF(refid = 0
(SELECT TOP 1 id FROM forum_posts ORDER BY id DESC)
(SELECT id FROM forum_posts WHERE id = t1.refid
) AS realI
FROM forum_posts t
ORDER BY id DES

Unfortunately, I want more than just the id column, I also want 'header', 'datecreated' and 'usr'. How do I go about it? THe only solution so far is to create an IIF statement for each column and just replace the criteria id in each. Is there another way?
 
M

Michel Walsh

Hi,


With Jet 4.0 (Access 2000 or later):



SELECT TOP 1
id, header, datecreated
FROM
(
SELECT refid=0 As MySwitch, id, header, datecreated
FROM forum_posts

UNION ALL

SELECT refid<>0, id, header, datecreated
FROM forum_posts
WHERE refid=id
)
ORDER BY mySwitch, id


Since True (-1) occurs numerically before False (0) in Jet, the logical
switch will pick up the required top 1 records.


In transact-SQL, use a stored procedure with a standard if testing refid and
use the appropriate SELECT accordingly. You can do the same, really, in VBA
too, but the exact formulation would depend of the context ( do you need the
string of the SQL statement, or a recordset).



Hoping it may help,
Vanderghast, Access MVP




Martin said:
Hi!
I'm trying to return a record that another referres to and have come to the follwing sql query:
SELECT TOP 1
IIF(refid = 0,
(SELECT TOP 1 id FROM forum_posts ORDER BY id DESC),
(SELECT id FROM forum_posts WHERE id = t1.refid)
) AS realID
FROM forum_posts t1
ORDER BY id DESC

Unfortunately, I want more than just the id column, I also want 'header',
Click to expand...
'datecreated' and 'usr'. How do I go about it? THe only solution so far is
to create an IIF statement for each column and just replace the criteria id
in each. Is there another way?
 
Top