SELECT query to return a row from a table with all values set to Null

Question

I need to make a query but get the value in every field empty. Gordon Linoff give me the clue to this need here: SQL Empty query results which is:

select t.*
from (select 1 as val
     ) v left outer join
     table t
     on 1 = 0;

This query wors perfectly on PostgreSQL but gets an error when trying to execute it in Microsoft Access, it says that 1 = 0 expression is not admitted. How could it be fixed to work on microsoft access?

Regards,

does `select t.* from table t where 1=0` give you what you're looking for? — nullrevolution, May 20 '14 at 13:03
@nullrevolution No, that is not what he wants. The query that he has is correct and works with PostgreSQL and T-SQL, but doesn't work on Access SQL — Radu Gheorghiu, May 20 '14 at 13:05

Gordon Linoff · Answer 1 · 2014-05-20T14:55:13.250

1

I am offering this answer here, even though you didn't think it worked in my edit to your original question. What is the problem?

select t.*
from (select max(col) as maxval from table as t
     ) as v left join
     table as t
     on v.val < t.col;

edited May 20 '14 at 14:55

answered May 20 '14 at 14:02

Gordon Linoff

1,242,037
58
646
786

Unfortunately, Access doesn't like `... join ... on val = 0`. It says "JOIN expression not supported." – Gord Thompson May 20 '14 at 14:26
@GordThompson . . . I just think that Access *should* support this method. I added a subquery, but you could really use any column and any value that is not equal. – Gordon Linoff May 20 '14 at 14:28
Access seems to have an "interesting" way of handling outer JOINs when one side of the ON condition is a constant. Now your query returns the same results as `SELECT * FROM table`. – Gord Thompson May 20 '14 at 14:32
@GordThompson . . . That was a typo, it should have been `0 as val` in the subquery. But as I say, you don't have to use a new column, you can use an existing one. – Gordon Linoff May 20 '14 at 14:36
Now your query returns 0 rows. :( – Gord Thompson May 20 '14 at 14:42

score 1 · Accepted Answer · answered May 20 '14 at 14:23

If the table has a numeric primary key column whose values are non-negative then the following query will work in Access. The primary key field is [ID].

SELECT t2.*
FROM
    myTable AS t2
    RIGHT JOIN
    (
        SELECT TOP 1 (ID * -1) AS badID 
        FROM myTable AS t1
    ) AS rowStubs
        ON t2.ID = rowStubs.badID

This was tested with Access 2010.

Joseph B · Answer 3 · 2014-05-20T13:44:42.720

You can use the following query, but it would still need a little "manual coding".

EDITS:

Actually, you do not need the SWITCH function. Modified query below.
Removed the reference to Description column from one line. Still, you would need to use a Text column name (such as Description) in the last line of the query.

For example, the following query would work for the Months table:

select Months.*
from Months
RIGHT OUTER JOIN
(select "" as DummyColumn from Months) Blank_Data
ON Months.Description = Blank_Data.DummyColumn; --hardcoded Description column

SELECT query to return a row from a table with all values set to Null

3 Answers3

Linked