query issue

[muralibobby2015]

hello,

few days back i attended one interview. he asked write a query for get the results from these tables.

there are two tables. one is for 'USER' and another is 'Properties'.
in the user table there is 3 feilds user_id,name and subscription_id.

in properties page property_id and user_id.

here is the table structure:

USER table:
user_id(primary), name, Subscription_id
1 , test , 3
2 , test1, 2
3 , test2 , 3
4 , test3 , 1

Properties table:
property_id(primary) , property_type, user_id(foreign)
1 , villa, 2
2 , home, 4
3 , land 1
4 , home, 2
5 , home, 3
6 , land, 3
7 , villa, 1
8 , home, 4

now i am searching 'home' using search form. then i have to display all the results which property type
is home in the order of subscription_id.

here now i have retrive properties in the order of subscription_id.

anybody tell how to retrive now. i am fully confused with this.

thanks

[urtrivedi]

SELECT p.property_id, p.property_type, u.user_id, u.name, u.subscription_id 
from properties p inner join user u on p.user_id=u.user_id
where p.property_type='home'
order by u.subscription_id

[muralibobby2015]

thanks for your replay

i have tried with real database. i have to display only properties which have user subscription id 2,3 . for this i wrote like this. but i am getting error. sorry upto now i never used joins. thats why i have little bit of confusion.

"select * from postadeal p INNER JOIN register r ON p.user_id=r.auto_id && p.status=1 order by r.subscription_id desc LIMIT 0,9"

[karthik_ppts]

I think no problem with query.. What error you got?

[urtrivedi]

post your mysql script with 2 table structure and sample data.