CODEWITHSUNDEEP
phpmysqlleft-join
Rynardt
Rynardt

Reputation: 5557

LEFT JOIN twice form same table

I am trying to join 4 fields from the same table twice with a table. I tried using two LEFT JOIN's and also two WHERE statements as below. Currently I am just not getting any results for the g2.address, g2.latitude, g2.longitude, g2.method. Not sure If I am doing something wrong with the statement or just some other obvious mistake related to my data. Any help would be appreciated.

1st Attempt:

SELECT p.q_id, p.first_name, p.surname, p.gender, p.age, p.race, 
       p.q2_7, p.q2_8_1, p.q2_8_2, p.q2_8_3, p.q2_8_4, p.q2_8_5, 
       p.q2_8_6, p.q2_8_7, p.q2_8_8, p.q2_9, p.q2_10, p.q2_11, 
       p.q2_11_1_1, p.q2_11_1_2, p.q2_11_1_3, p.q2_11_1_4, 
       p.q3_1, p.q3_2, p.q3_2_1, p.q3_3, p.q3_4, p.q3_5, 
       g1.address, g1.latitude, g1.longitude, g1.method, 
       p.q3_7, p.q3_8, p.q3_9, p.q3_10, p.q3_11_1, p.q3_11_2, 
       p.q3_11_3, p.q3_12, p.q3_13, p.q4_1, 
       g2.address, g2.latitude, g2.longitude, g2.method, 
       p.q4_3, p.q4_4, p.q4_5, p.q5_0_1, p.q5_0_2, p.q5_0_3, 
       p.q5_1, p.q5_2, p.q5_3
  FROM people AS p 
  LEFT JOIN gmap_address_list AS g1 ON p.q3_6 = g1.id 
  LEFT JOIN gmap_address_list AS g2 ON p.q4_2 = g2.id 
 GROUP BY p.q_id

2nd Attempt

SELECT p.q_id, p.first_name, p.surname, p.gender, p.age, p.race, 
       p.q2_7, p.q2_8_1, p.q2_8_2, p.q2_8_3, p.q2_8_4, p.q2_8_5, 
       p.q2_8_6, p.q2_8_7, p.q2_8_8, p.q2_9, p.q2_10, p.q2_11, 
       p.q2_11_1_1, p.q2_11_1_2, p.q2_11_1_3, p.q2_11_1_4, p.q3_1, 
       p.q3_2, p.q3_2_1, p.q3_3, p.q3_4, p.q3_5, 
       g1.address, g1.latitude, g1.longitude, g1.method, 
       p.q3_7, p.q3_8, p.q3_9, p.q3_10, p.q3_11_1, p.q3_11_2, 
       p.q3_11_3, p.q3_12, p.q3_13, p.q4_1, 
       g2.address, g2.latitude, g2.longitude, g2.method, 
       p.q4_3, p.q4_4, p.q4_5, p.q5_0_1, p.q5_0_2, p.q5_0_3, 
       p.q5_1, p.q5_2, p.q5_3
  FROM people p, gmap_address_list g1, gmap_address_list g2 
 WHERE p.q3_6 = g1.id AND p.q4_2 = g2.id 
 ORDER BY p.q_id

Upvotes: 1

Views: 232

Answers (2)

Rynardt
Rynardt

Reputation: 5557

Got it working by changing to:

g2.address AS address_2, g2.latitude AS latitude_2, g2.longitude AS longitude_2, g2.method AS method_2

I am using $row = mysql_fetch_array($result_people, MYSQL_ASSOC) which resulted in duplicate entries for address, latitude, longitude, method

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

Without understanding exactly how p.q3_6 and p.q4_2 get populated, it's hard to guess why your second LEFT JOIN might not match anything. But, you do have some duplicate column names in your queries' result sets; this might baffle the array-loading stuff in php. You might try the following:

SELECT ... ,
       g1.address AS g1_address, g1.latitude AS g1_latitude, 
       g1.longitude AS g1_longitude, g1.method AS g1_method, 
       ... ,
       g2.address AS g2_address, g2.latitude AS g2_latitude, 
       g2.longitude AS g2_longitude, g2.method AS g2_method, 
       ...

Note, your second query: no good for a left join. Stick with the first one.

Upvotes: 1

Related Questions