Joining tables

[derek mcgilvray]

Hi folks,
No matter what book I read or tutorial I try to follow, I can't quite seem to get this right. Can anyone tell me what's wrong with this query please?

$query="SELECT table1.var1, table1.var2, table1.var3, table2.var1, table2.var4, table3.var1, table3.var5 WHERE table1.var1=table2.var1 AND table2.var1=table3.var1 ORDER BY var4 DESC, var5 ASC";

$result = mysql_query($query)or die (mysql_error());

$num_res=mysql_num_rows($result);

Any help much appreciated,

Derek

[Scally_Ally]

There doesnt look to be anything wrong with your query, apart from the ordering by, where you arent specifying which table it is on.

Make sure that there is an actual match maybe (there is a var that matches from table 1 to table 2, and then on that row there is a match in table 3, if you see what i mean)

Also maybe try using the explain statement in Sql
"EXPLAIN SELECT * FROM table1"; see if that gives you any insight.

Ally

[dreamcatcher]

Hi Derek,

You may want to give us a little more information. When you say you can`t get it right, are you seeing errors? Or is the query returning an incorrect result set?

dc

[barns101]

Isn't it still necessary to include a FROM clause? e.g.

$query = "SELECT table1.var1, table1.var2, table1.var3, table2.var1, table2.var4, table3.var1, table3.var5 FROM table1, table2, table3 WHERE table1.var1=table2.var1 AND table2.var1=table3.var1 ORDER BY var4 DESC, var5 ASC";

[derek mcgilvray]

Hi all,

Thanks for your help. Useful suggestions such as EXPLAIN will be used again. Barns101 - you hit the nail on the head - I missed out the FROM part of the query - funny the tutorial I used didn't use it either! But it works now and I can move on. Thanks again,

Derek

[derek mcgilvray]

OK, I thought I had it, but it's not displaying all my results. Also, they order by var2 but not by var3.

$query="SELECT
table1.var1,
table1.var2,
table1.var3,
table2.var1,
table2.var2,
table3.var1,
table3.var2
FROM table1, table2, table3
WHERE table1.var1 = table2.var1
AND table3.var1 = table2.var1
ORDER BY table1.var2 DESC, table1.var3 ASC";
$result = mysql_query($query)or die (mysql_error());
$num_res=mysql_num_rows($result);

echoed the query and it looks ok. Any ideas?

Derek

[barns101]

If it's displaying some results then at least the syntax is correct. The problem is likely to be one of logic (i.e. the way it's written will not pick out certain results by design, not through a fault). Hope that makes sense!

The results are ordered by table1.var2 and then by table1.var3, but remember that table1.var3 only kicks in if two table1.var2 records are the same value.

[derek mcgilvray]

Thanks for your suggestion ScallyAlly. I thought it was too obvious at the time, but there were some differences in two tables due to the fact that one was newer than the other, hence t1.var!= t2.var1 in all instances! All sorted now. Lesson learned.

Thanks again.