how to apply join query using SQL

Question

I have 2 tables in a MySQL database.

The first table is 'property' with columns (id(pk), title, description, status)

The second table is 'object_property' in which all the relations between object and property is stored and their relation value, having columns (id(pk), object_id, property_id(fk reference column is 'id' of property table), value)

Now i want to extract a result of all properties from 'property' table where the relation doesn't exist in the 'object_property' table.

I need to write the SQL join query for this...

Can anybody help me out? I am new to SQL.

Thanks!

Answer 1

If you were writing a raw query to run directly in MySQL you could use the following LEFT JOIN:

SELECT p.*
FROM property p
LEFT JOIN object_property op
    ON p.id = op.property_id
WHERE op.property_id IS NULL

If you were writing a query in HQL, then you would be dealing with entities (Java POJOs) representing tables, rather than tables themselves. I can offer the following HQL query:

from Property p 
left join p.ObjectProperty as op
where op is null

This assumes that you have a class called Property which represents the property table. It also assumes that the object_property table has an entity class ObjectProperty, and that Property has a reference to ObjectProperty.