Query returning the same rows 6 times each

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Query returning the same rows 6 times each

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

looked great but it did not work, the same stuff is being returned.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

feyd, in the strange way that it is possible, I love you.
Post Reply