0

the following should return 1 row

$search= "Timothy";

$query4 = mysql_query("SELECT * FROM test1, test2, combination WHERE test1.IDONE = `combination`.IDONE AND test2.IDTWO = combination.IDTWO AND test1.NAME LIKE '%$search%'",$this->connect) or die(mysql_error());

instead it returns zero.

the query should take values from all three tables according to the where clause.

in short to describe the table structure.

3 tables(test1, test2, combination)

test1 has primary key IDONE, test2 has primary key IDTWO

combination looks like this

CREATE TABLE `combination` (
IDONE int(8) NOT NULL,
IDTWO varchar(11) NOT NULL,
INFO char(200) NOT NULL,

INDEX (IDONE, IDTWO),
PRIMARY KEY (IDONE,IDTWO),
FOREIGN KEY (IDTWO) REFERENCES `test2` (IDTWO)  ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (IDONE) REFERENCES `test1` (IDONE) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

i havent done the relational database wrong.. there is no mysql_errors either. any idea?

4
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by griswolf
0

Are you sure about these quotes around combination? I've never done that when using sql.

WHERE test1.IDONE = `combination`.IDONE

0

@drjohn. Those back quotes around table or column names are legal (and often a good idea). I tend not to do it that way because I'm a lazy typist. I'd have written

"SELECT * FROM test1 as t1, test2 as t2, combination as c WHERE t1.IDONE = c.IDONE AND t2.IDTWO = c.IDTWO AND t1.NAME LIKE '%$search%'"

add:
In fact, I'd use an explicit join:

"SELECT * FROM test1 as t1 join test2 as t2 join combination as c on t1.IDONE = c.IDONE AND t2.IDTWO = c.IDTWO where t1.NAME LIKE '%$search%'"

Edited by griswolf: add a join select

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.