QUOTE(nahelna @ 22 Aug, 2008 - 01:03 AM)
when i run this query it does notgive me all the problems registered in the database, why?
where hav i gone wrong?
Well, the only thing that springs to mind is inconsistent data - one of the join fields in your problems table has a value that doesn't exist in the table you're joining on.
The comma-join you're using is logically equivalent to the inner join posted by Computer_. In an inner join, only rows where the join column value exists in both tables are in the result. So if, for example, your problems table has a row with userid = 3, but userid 3 has been deleted from the users table, then that problem will not show up in the query result.
The easy solution to that is simply to use an outer join. For example
SQL
SELECT ... FROM problems p LEFT JOIN category c ON p.catid = c.catid ...
With a left outer join,
all rows from the table on the left-hand side of the join will always be in the result, and vice versa for a
right outer join.
You might also consider adding foreign key constraints on those join columns. That way, you don't have to worry about about missing values, as the DBMS will simply fail any insert or update that would create inconsistent data. But, of course, you would have to clean up your data before you could add the constraints. Assuming that's even the problem - I don't know that for sure.
| 