Trouble with JOIN

[fizix]

Ok, I've got two tables:

 

Table: ips
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      |      | PRI | NULL    | auto_increment |
| connect   | tinyint(1)   |      |     | 0       |                |
| ip        | bigint(20)   |      |     | 0       |                |
| domain    | varchar(100) |      |     |         |                |
| title     | varchar(250) |      |     |         |                |
| time      | int(11)      |      | MUL | 0       |                |
| title_key | int(2)       |      |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)


Table: title_keys
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(4)       |      | PRI | NULL    | auto_increment |
| title | varchar(250) |      |     |         |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

 

I'm trying to LEFT JOIN the two tables so that title.title_keys becomes the title when ips.title_key matches id.title_keys. In all other instances I'd like it to return ips.title. I've made sure that ips.title is always empty when ips.title_key is not 0. Here is my query:

 

SELECT * FROM `ips` LEFT JOIN `title_keys` ON ips.title_key = title_keys.id

 

However, when I echo mysql_result($recentfound,$x,"title") it only echoes the title from ips.title. I thought if ips.title was blank and title_keys.id matched ips.title_key it would echo title_keys.title. What am I doing wrong?

[kickstart]

Hi

 

There is nothing in there to specify which title field to bring back. It will consistantly bring back one of the title fields whether it is null or not.

 

I think to do what you want to do you would need to use an IFNULL within the field list to choose which title field to bring back for each row

 

Something like

 

SELECT IFNULL(ips.title,title_keys.title) 
FROM `ips` 
LEFT JOIN `title_keys` 
ON ips.title_key = title_keys.id 

 

All the best

 

Keith