JOIN on Subquery

Question

I want to execute a simple join on two tables after connecting two columns with ids on one of these tables.

First step:

SELECT cars.hhid & cars.vmid
FROM cars

Now I want to compare this result with another table (table2).

The new result should be every row in table1 (cars) that match with id in table2.

What’s wrong?

My code:

SELECT Cars.* 
FROM (SELECT Cars.hhid & Cars.vmid AS zid
FROM cars) x
JOIN table2 ON table2.id = x.zid;

Answer 1

From what you've said in the question, I don't see why you need a subquery at all.

Try this:

select cars.*
from cars 
inner join table2 on cstr(table2.id) = cars.hhid & cars.vmid

(tested with double as data type for all three fields)

You don't need the subquery because you can put your "connected columns" directly into the JOIN clause (of course you can put them into the SELECT clause as well if you need them there).

However, as soon as you concatenate the columns, Access seems to treat them as a string, so you can't join them directly on the double column in table2.
That's why you need to convert the column from table2 to a string with CStr().

Answer 2

Try

SELECT x.* 
FROM (SELECT Cars.hhid & Cars.vmid AS zid
FROM cars) x
INNER JOIN table2 ON table2.id = x.zid;

MS Access like the join type to be specified.

Answer 3

* Answer for TSQL, prior to learning it was MS-Access *

SQL has no double data type, so you'll need to use float.

To use the concatenation operator + the values will need to be converted to a string using CAST or CONVERT

Data:

declare @cars table (hhid float, vmid float)
declare @table2 table (id float)

insert @cars values (1,1),(2,2)
insert @table2 values (11),(22),(12),(21)

Query:

SELECT x.* 
FROM (
    SELECT CAST(c.hhid AS nvarchar(20)) + CAST(c.vmid AS nvarchar(20)) AS zid
    FROM @cars c
    ) x
INNER JOIN @table2 t ON t.id = x.zid

* Attempt at MS-Access *

Does this work any better, to me it looks like your table references are slightly wring

SELECT x.* 
FROM (SELECT c.hhid & c.vmid AS zid
FROM cars c) x
JOIN table2 t ON t.id = x.zid;