CODEWITHSUNDEEP
sqlselectjoin
makambi
makambi

Reputation: 779

Use SELECT as value for JOIN

I have table ItemPropertyValue with unique ID and PropertyID

ID IDProperty Value ItemID
1  1            1    5 
2  2            2    6
3  3            2    10
4  4            2    15

And another table called PropertyCategory

IDProperty  Value  Name
1           1      First Option
1           2      Second Option
2           1      Another option

What I want - it's to select PropertyID from first table ItemPropertyValue where ItemID = 10 and then join with second table PropertyCategory

So I have smth like this:

IDProperty  Value  Name
1           1      First Option
1           2      Second Option
2           1      Another option

I tried this but there is an error. No such column as PropertyID

    SELECT * FROM PropertyCategory JOIN 
    (SELECT ItemPropertyValue.IDProperty AS PropertyID
    WHERE ItemPropertyValue.IDItem = '10') 
    ON PropertyCategory.IDProperty = PropertyID

How can I do it?

MS SQLServer if any

Upvotes: 1

Views: 5240

Answers (4)

eouw0o83hf
eouw0o83hf

Reputation: 9598

Try Aliasing the subselect

SELECT * FROM PropertyCategory JOIN 
(SELECT ItemPropertyValue.IDProperty AS PropertyID
WHERE ItemPropertyValue.IDItem = '10') a
ON PropertyCategory.IDProperty = a.PropertyID

Additionally, this would do well as a regular join with the filtrating happening in the outer WHERE clause, like

SELECT *
FROM PropertyCategory
JOIN ItemPropertyValue ON PropertyCategory.IDProperty = ItemPropertyValue.IDProperty
WHERE ItemPropertyValue.IDProperty = '10'

Finally, if you happen to be using SQL Server and really want to do it in a subselect-type of statement, I'd suggest checking out CROSS APPLY or OUTER APPLY for such an application.

Upvotes: 3

Zia
Zia

Reputation: 921

you are doing fine, just add alias to table name and it will work 

SELECT * FROM PropertyCategory a JOIN 
        (SELECT ItemPropertyValue.IDProperty AS PropertyID
        WHERE ItemPropertyValue.IDItem = '10')  b
        ON a.IDProperty = b.PropertyID

Upvotes: 0

Stephane Paquet
Stephane Paquet

Reputation: 2344

I would go for something like

SELECT * FROM ItemPropertyValue JOIN PropertyCategory

ON ItemPropertyValue.IDProperty = PropertyCategory.IDProperty WHERE ItemPropertyValue.ItemID=10

You can restrict to couple of field in your SELECT by replacing the * with fields.

Upvotes: 1

Minras
Minras

Reputation: 4346

Use table alias:

SELECT * 
    FROM PropertyCategory 
    JOIN 
        (SELECT ItemPropertyValue.IDProperty AS PropertyID 
            WHERE ItemPropertyValue.IDItem = '10') as T
        ON PropertyCategory.IDProperty = T.PropertyID

Upvotes: 2

Related Questions