Multiple object properties SQL query issue

[motivatedgorilla]

Hi,

I have query below however am unable to execute it. It keeps returning the error/message that there is a syntax error in FROM clause. What am I missing? The model is running off an EAP project file.

Code: [Select]

SELECT * FROM t_object INNER JOIN (SELECT * FROM t_objectproperties tag WHERE tag.Property = 'Location' AND tag.Value = 'Queens') AS location ON t_object.Object_ID = location.Object_ID INNER JOIN (SELECT * FROM t_objectproperties tag WHERE tag.Property = 'Room' AND tag.Value = 'King') AS type ON t_object.Object_ID = type.Object_ID

[Geert Bellekens]

Probably because you are trying to join subqueries.
Why would you want to do that?

Geert

[qwerty]

EAP == MS Access == not really a RDBMS but some Mickeysoft lookalike. It eats only a very redundant subset of SQL.

q.

[motivatedgorilla]

Hi Geert,

I am attempting to filter a result set. For example, the first query would return all results that matched location based in Queens. The second query would return results of all rooms in Queens that were King. How else would I be able to achieve this?

[Geert Bellekens]

Just use joins between tables iso joins between subqueries
In a proper DBMS this works

Code: [Select]

select * from t_object o
inner join t_objectproperties location on o.Object_ID = location.Object_ID
                              and location.Property = 'Location'
                              and location.Value = 'Queens'
inner join t_objectproperties _type on o.Object_ID = _type.Object_ID
                              and _type.Property = 'Room'
                              and _type.Value = 'King'
In MS Access you'll have to use this:

Code: [Select]

select * from ((t_object o
inner join t_objectproperties location on location.Object_ID = o.Object_ID)
inner join t_objectproperties ttype on ttype.Object_ID = o.Object_ID)
where
location.Property = 'Location'
and location.Value = 'Queens'
and ttype.Property = 'Room'
and ttype.Value = 'King'
Geert