Sql - return value for EVERY thing being searched in select command, even if repeated, or doesn't exist

Question

As the title says I want to run a select statement to return 1 value for each item searching with. I will provide a simple example for this.

Lets say that my table is the following:

table1

Mdl       Code
Model     Model Code
M1070     32HT
M1060     32A4

where table1 is the table, Mdl and Code are the columns, and there are two entries.

Now lets say that I have a list that makes it so that I need to convert from model codes to models. This list can have model codes found in table1, it can have entries not found in table1, and it can have entries that repeat in the search. My question is how (in a generalized sense since my data is more complex) can I run the following example of a search:

32HT
32HT
32HM
NULL (blank entry in searching)

I would ideally like it to return the following:

M1070
M1070
"" (a null or blank value as a place holder so the lists stay in line)
"" (a null or blank value again)

The importance of this is that the return values must always return something or the list it is being dumped into can get off.

Thanks ahead of time.

To clear some things up, the list would be a generated list (so not from an existing table). I am also using JET SQL so that limits some of the SQL functions available.

score 1 · Answer 1 · answered Oct 25 '11 at 19:12

1

SELECT l.Code, COALESCE(t.mdl, '') as mdl
    FROM List l
        LEFT JOIN table1 t
            ON l.Code = t.Code

answered Oct 25 '11 at 19:12

Joe Stefanelli

132,803
19
237
235

This is along the right track but the list would be inputted (generated). Sorry for the confusion – Eric F Oct 25 '11 at 20:14
@EricF I'd suggest you store that inputted list in a temporary table structure and use the query given. – Joe Stefanelli Oct 25 '11 at 20:18
Could you please provide an example of this? Sorry for the lack of experience – Eric F Oct 25 '11 at 20:25
2

"JET SQL" implies the Access database engine, in which case temporay tables are not an option and `COALESCE()` is not available (but may be rewritten as `IIF(t.mdl IS NULL, '', t.mdl)` ). – onedaywhen Oct 26 '11 at 08:37
SQL is a generic tag, jet-sql is specific. – Fionnuala Oct 26 '11 at 10:26

Ashlin · Answer 2 · 2011-10-25T19:31:28.873

-1

Is the list coming from another table? If so, an Outer Join between the two tables should preserve the null values.

SELECT * FROM listtable
LEFT OUTER JOIN table1

edited Oct 25 '11 at 19:31

answered Oct 25 '11 at 19:17

Ashlin

26
6

No, the search parameters would be inputted. Sorry for the confusion – Eric F Oct 25 '11 at 20:14
@EricF I would recommend reviewing [this article](http://msdn.microsoft.com/en-us/library/aa173839%28v=sql.80%29.aspx) for notes on how to write data to a table, and just declare a temporary table to join your existing table with. – Ashlin Oct 25 '11 at 20:49

Sql - return value for EVERY thing being searched in select command, even if repeated, or doesn't exist

2 Answers2