MS Access SQL: select rows with the same order as IN clause

Question

I know that this question has been asked several times and I've read all the answer but none of them seem to completely solve my problem.

I'm switching from a mySQL database to a MS Access database. In both of the case I use a php script to connect to the database and perform SQL queries.

I need to find a suitable replacement for a query I used to perform on mySQL. I want to:

1. perform a first query and order records alphabetically based on one of the columns
2. construct a list of IDs which reflects the previous alphabetical order
3. perform a second query with the IN clause applied with the IDs' list and ordered by this list.

In mySQL I used to perform the last query this way:

SELECT name FROM users WHERE id IN ($name_ids) ORDER BY FIND_IN_SET(id,'$name_ids')

Since FIND_IN_SET is available only in mySQL and CHARINDEX and PATINDEX are not available from my php script, how can I achieve this?

I know that I could write something like:

SELECT name 
  FROM users 
 WHERE id IN ($name_ids) 
ORDER BY CASE id
           WHEN ... THEN 1 
           WHEN ... THEN 2
           WHEN ... THEN 3
           WHEN ... THEN 4
         END

but you have to consider that:

• IDs' list has variable length and elements because it depends on the first query
• that list can easily contains thousands of elements

Have you got any hint on this? Is there a way to programmatically construct the ORDER BY CASE ... WHEN ... statement? Is there a better approach since my list of IDs can be big?

UPDATE: I perform two separated query because I need to access two different tables. The databse it's not very simple so I try to make an example: Suppose I have a table which contains a list of users and a table which contains all the books that every user have in their bookshelf.

Since the dabase was designed in mySQL, for every book record I store the user_id in the books table in order to have a relationship between the user and the book.

Suppose now that I want to obtain a list of all the user that have books with a title starting with letter 'a' and I want to order the user based on the alphabetical oder of the books. This is what I do:

1. perform a first query to find all the books which start with letter 'a' and sort the alphabetically
2. create a list of user_id which should reflect the alphabetical order of the book
3. perform a query in the users table to find out the users names and sort them with the user_id list to have the required sorting by book

Hope this clarify what I need.

Answer 1

If I understand correctly, you're trying to get a set of information in the same order that you specify the ID values. There is a hack that can convert a list into a table using XML and CROSS APPLY. This can be combined with the ROW_NUMBER function to generate your sort order. See the code below:

CREATE FUNCTION [dbo].[GetNvarcharsFromXmlArray] 
(   
    @Strings xml = N'<ArrayOfStrings/>'
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT     ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber, Strings.String.value('.', 'nvarchar(MAX)') AS String
    FROM          @Strings.nodes('/ArrayOfStrings/string/text()') AS Strings(String)
)

Which functions with the following structure:

<ArrayOfStrings>
    <string>myvalue1</string>
    <string>myvalue2</string>
</ArrayOfStrings>

It's also the same format .NET xml serializes string arrays.

If you want to pass a comma separated list, you can simply use:

CREATE FUNCTION [dbo].[GetNvarcharsCSV] 
(   
    @CommaSeparatedStrings nvarchar(MAX) = N''
)
RETURNS TABLE 
AS
RETURN 
(
        DECLARE @Strings xml
        SET @Strings = CONVERT(xml, N'<ArrayOfStrings><string>' + REPLACE(@CommaSeperatedStrings, ',', N'</string><string>') + N'</string></ArrayOfStrings>')

    SELECT     ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber, Strings.String.value('.', 'nvarchar(MAX)') AS String
    FROM          @Strings.nodes('/ArrayOfStrings/string/text()') AS Strings(String)
)

This makes your query:

SELECT name
FROM users
INNER JOIN dbo.GetNvarcharsCSV(@name_ids) AS IDList ON users.ID = IDList.String
ORDER BY RowNumber

Note that it's a pretty simple rewrite to make the function return a table of integers if that's what you need.

You can see xml Data Type Methods to get a better understanding of what you can do with XML in SQL queries. Also, see ROW_NUMBER (Transact-SQL).

Answer 2

It sounds like you need a JOIN...
This should work, although it may need to be translated to Access syntax (which is apparently subtly different):

SELECT b.name, a.title
FROM book as a
JOIN user as b
ON b.id = a.userId
WHERE SUBSTRING(LOWER(a.title), 1, 1) = 'a'
ORDER by a.title

I don't know why you're switching to Access, although I have heard it's been improving in recent years. I think I'd prefer almost any other RDBMS, though. And your schema could probably stand some tweaking, from the sound of things.

Answer 3

You would have to use a user-defined function that maintains the order, and then order by that column. For example:

CREATE FUNCTION dbo.SplitList
(
    @List VARCHAR(8000)
)
RETURNS TABLE
AS
  RETURN
  (
    SELECT DISTINCT
      [Rank],
      [Value] = CONVERT(INT, LTRIM(RTRIM(SUBSTRING(@List, [Rank],
        CHARINDEX(',', @List + ',', [Rank]) - [Rank]))))
    FROM
    (
      SELECT TOP (8000) [Rank] = ROW_NUMBER()
        OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
    ) AS n
    WHERE [Rank] <= LEN(@List)
      AND SUBSTRING(',' + @List, [Rank], 1) = ','
);
GO

Now your query can look something like this:

SELECT u.name 
  FROM dbo.users AS u 
  INNER JOIN dbo.SplitList($name_ids) AS s
  ON u.id = s.Value
  ORDER BY s.[Rank];

You may have to surround $name_ids with single quotes (dbo.SplitList('$name_ids')) depending on how the SQL statement is constructed. You may want to consider using a stored procedure instead of building this query in PHP.

You might also consider skipping MS-Access as a hopping point altogether. Why not just have PHP communicate directly with SQL Server?