Why does this MySQL Query returns two rows?
my query returns two rows where second is the exact duplicate of first. i would like to know the reason why is it happening? i know if i put the LIMIT 1 in the end it will return only a single row so i would request not to suggest this solution. here is my query
SELECT u.username,u.password,u.registerDate,u.lastVisitDate,u.lastVisitIp,
u.activationString,
u.active,u.block,u.contact_id,c.name,c.email,c.pPhone,c.sPhone,c.rPhone,c.area_id,
a.name as areaName, a.city_id, ct.name as cityName, ct.state_id, s.name as stateName,
s.country_id, cn.name as countryName
FROM users u
LEFT JOIN conta开发者_运维百科cts c
ON (u.contact_id = c.id)
LEFT JOIN areas a
ON (c.area_id = a.id)
LEFT JOIN cities ct
ON (a.city_id = ct.id)
LEFT JOIN states s
ON (ct.state_id = s.id)
LEFT JOIN countries cn
ON (s.country_id = c.id)
WHERE u.id = 1
Above query is fetching data from 6 tables which includes
users s
contacts c
areas a
cities ct
states s
country cn
all 6 tables above have primary key named id
the foreign keys are as follows
users.conntact_id
contacts.area_id
areas.city_id
cities.state_id
states.country_id
why is it returning two rows instead of one?
One of the tables you join into has more than one element, thus all the others are repeated. This table most probably is the contacts
table.
精彩评论