CODEWITHSUNDEEP
mysql
keya
keya

Reputation: 29

mysql query result issue with join

I have multiple tables as table_1 has id , p_code, profile_status, name and table_2 has id, p_code, availablity and table_3 has id, p_code, status... How to get all records form all tables depend on p_code. table_2 and table_3 has few records. if p_code not in table_2 and table_3 then echo 'no' in results. currently i am using my query as below

select t.id, t.p_code,t.name,t.num_rooms, t.profile_status, t.distance FROM (  
     (        SELECT id , p_code, profile_status, name,num_rooms, 3956 * 2 *  ASIN(SQRT( POWER(SIN(($origLatAirport - latitude)*pi()/180/2),2)
      +COS($origLatAirport*pi()/180 )*COS(latitude*pi()/180)
      *POWER(SIN(($origLonAirport-longitude)*pi()/180/2),2))) 
      as distance FROM property WHERE   profile_status=1 having distance < ".$dist." )  ) as t

How to add table_2 and table_3 and fetch results. Pleasr reply soon. I am stuck here.

Upvotes: 0

Views: 39

Answers (1)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

In your query you are doing CROSS JOIN and what you desire, is probably INNER JOIN.

In MySQL the CROSS JOIN behaves like JOIN and INNER JOIN of without using any condition. The CROSS JOIN returns all rows form user multiplied by all rows from user_inbox - for every user you get inboxes of all users.

You should specify condition for your JOIN statement.

 $sql_alt = mysql_query(
    "select i.*,u.images, u.firstname, u.lastname 
         from user_inbox i INNER JOIN user u ON i.to_id = u.user_id
         where i.to_id = '$user_id'");

Also it is good habit have the same names for primary and foreign keys, so I think you should have user_id or user_id_to instead of to_id in your user_inbox table. This is of course not absolutely necessary.

Upvotes: 2

Related Questions