Return multiple columns with IIF?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
 
Back
Top