Why is this SQL query returning repeated records, when there not repeated in the database?

SELECT *
FROM support_systems,tickets
INNER JOIN user_access ON tickets.support_system_id = user_access.support_system_id
WHERE support_systems.account_id = #session.account_id# 
AND user_access.user_access_level >= 1 
AND user_access.user_id = #session.user_id# 
开发者_开发知识库

Any clue why this query would return a record set with repeated records? The results are looking like this:

Priority    ID      Subject         Status
high        1       First Subject   open
high        1       First Subject   open
low         3       Weeee           open
low         3       Weeee           open
medium      4       hhhhh           closed
medium      4       hhhhh           closed
medium      5       neat            open
medium      5       neat            open

Let me know if you guys need more information, thanks a lot.

You are selecting records from the table support_system but have not specified the join condition. What is the relationship between this table and the others you are interrogating?

You may want something like this

SELECT *
FROM support_systems 
INNER JOIN tickets ON 
support_systems.support_system_id = tickets.support_system_id
INNER JOIN user_access ON 
tickets.support_system_id = user_access.support_system_id

WHERE support_systems.account_id = #session.account_id# 
AND user_access.user_access_level >= 1 
AND user_access.user_id = #session.user_id# 

The problem is this line:

FROM support_systems,tickets

I would remove the tickets from the FROM clause and make it an inner join clause. Right now you have what's called a "cross product": http://en.wikipedia.org/wiki/Cross_product

I would have to say its probably becuase you have an explicite join and a non explicite join which isnt handled in the where which is producing a cartesian...

you have three tables...

but only two tables used in the join... you need a 2nd join... you need to include support_systems in your join somewhere.

probably like

from support_systems a left join user_access b on a.support_systems_id = b.support_systems_id
left join ticket c on c.support_systems_id = b.support_systems_id

then your where would be the same... and it would return based on the correctly joined tables.