CODEWITHSUNDEEP
sqlms-access
inetphantom
inetphantom

Reputation: 2617

How to select top 1 ID from each of two not joinable tables?

I got an n to n relation on my db and want to now fill up the connecting table in the middle.

I have the same issue as that guy here, but my two tables are not directly related.

So my first try was:

INSERT into foo_has_bar (foo_ID, bar_ID) VALUES (
  (Select TOP 1 ID from foo where foo_cond = "TRUE"),
  (Select TOP 1 ID from bar where bar_cond = "TRUE")
)

Now this is invalid and returns

Query input must contain at least one table or query

When now trying to create it in the form as described in the link above I would need something like this:

INSERT into foo_has_bar (foo_ID, bar_ID)
Select foo.ID, bar.ID FROM foo, bar
...
)

But how can I now merge the SELECT TOP 1 for each of those and the WHERE also only for the corresponding (foowont have a bar_cond)?

Upvotes: 1

Views: 56

Answers (2)

inetphantom
inetphantom

Reputation: 2617

The actual solution I found through the hints in that question:

INSERT into foo_has_bar (foo_ID, bar_ID)
VALUES (DLOOKUP("ID", "foo", "foo_cond = ""TRUE"""),
        (DLOOKUP("ID", "bar", "bar_cond = ""TRUE"""),
       );

Awful escaping in ACCESS..

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would expect this query to work in MS Access:

INSERT into foo_has_bar (foo_ID, bar_ID)
    VALUES ((Select TOP 1 ID from foo where foo_cond = "TRUE"),
            (Select TOP 1 ID from bar where bar_cond = "TRUE")
           );

But if it doesn't, try this:

INSERT into foo_has_bar (foo_ID, bar_ID)
    SELECT f.ID, b.ID
    FROM (Select TOP 1 ID from foo where foo_cond = "TRUE"
         ) as f,
         (Select TOP 1 ID from bar where bar_cond = "TRUE"
         ) as b;

As a note: Normally you should use ORDER BY with TOP, so which id you get is consistent.

Upvotes: 2

Related Questions