Page 1 of 1

Query returning the same rows 6 times each

Posted: Tue Mar 07, 2006 1:12 pm
by shiznatix

Code: Select all

SELECT
  *
FROM
  webmaster AS base
LEFT JOIN tasks AS sub ON sub.fk_type = "webmaster"
WHERE
  sub.fk_id != base.webmaster_id
AND
  base.date != "1141758231"
AND
  base.old != 1
ORDER BY
  base.date
DESC
ok strange for me. there are 6 rows in tasks where fk_type = "webmaster" and sub.fk_id != base.webmaster_id so the 6 makes sence in a way. but why for every 1 row in base it returns it 6 times? What is wrong with that query?

edit: to clarify what I mean, I want it to return this

Code: Select all

Who done it?
w4hweherh
blabla
but instead it returns

Code: Select all

Who done it?
Who done it?
Who done it?
Who done it?
Who done it?
Who done it?
w4hweherh
w4hweherh
w4hweherh
w4hweherh
w4hweherh
blabla
blabla
blabla
blabla
blabla
blabla

Posted: Tue Mar 07, 2006 1:18 pm
by feyd

Code: Select all

SELECT
  DISTINCT *
FROM
  webmaster AS base
LEFT JOIN tasks AS sub
ON sub.fk_id != base.webmaster_id
WHERE
  sub.fk_type = "webmaster"
AND
  base.date != "1141758231"
AND
  base.old != 1
ORDER BY
  base.date
DESC

Posted: Tue Mar 07, 2006 1:29 pm
by shiznatix
looked great but it did not work, the same stuff is being returned.

Posted: Tue Mar 07, 2006 1:33 pm
by feyd
distinct has to take into account all the data found in the resulting record, so change the asterix to the field(s) you actually want to find information on.

Posted: Tue Mar 07, 2006 1:47 pm
by shiznatix
feyd, in the strange way that it is possible, I love you.