am having a duplicate result because of bids.
i need to limit the result for only auctions to 1, i tried "limit 1" but they all get limited to 1 both bids n auctions, how do i limit only auctions to 1
how do i go about this, thanks.

$query = "SELECT b.*, u.nick, a.id, a.team1, a.team2 FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "auctions a ON (a.id = b.auction)
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);

There are many ways to approach it, but without seeing the schema of your table,
I can tell you that what you need is group by

e.g.

$query = "SELECT b.*, u.nick, a.id, a.team1, a.team2 FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "auctions a ON (a.id = b.auction)
group by b.id
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);

dhtml18:
There are many ways to approach it, but without seeing the schema of your table,
I can tell you that what you need is group by

e.g.

$query = "SELECT b.*, u.nick, a.id, a.team1, a.team2 FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "auctions a ON (a.id = b.auction)
group by b.id
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);

group by id reduced both to 1 result was hoping to reduce a.id to 1 n let d rest by as they are

Hmn, i rest my case then. My advice is to study well each of the things you use, that way you wil figure out what the best thing to use is.

dhtml18:
Hmn, i rest my case then. My advice is to study well each of the things you use, that way you wil figure out what the best thing to use is.

already figure it out n d method u said didnt work, sooooooooo.

skyhighweb:
already figure it out n d method u said didnt work, sooooooooo.

It is good that you have figured it out, you should have mentioned your solution then - maybe it might help someone.
Figuring out how to sort out a JOIN JAM without having the tables is not exactly simple.

dhtml18:

It is good that you have figured it out, you should have mentioned your solution then - maybe it might help someone.
Figuring out how to sort out a JOIN JAM without having the tables is not exactly simple.

make another seperate query, thus not including left join

I see, you could have used join without left - that wont give you duplicates. And there is a way to use left join still and combine with the is not null to avoid duplicates.

easier said than done

O yeah, I have queries I have written in time past to join like 20 tables even on complex platforms like oxwall, drupal, wordpress. I am talking of queries like 20 lines long.

Take a look at this for example (and i had no duplicates)
facebook.com/dhtml5/posts/10210921119757880

Having unnecessary duplicates usually means that you have not mastered a particular something, or that there is an error in planning of your tables.
You need to know where to use just join, left join, subquery e.t.c And you failed to submit your correction here for scrutiny.

skyhighweb:
am having a duplicate result because of bids.
i need to limit the result for only auctions to 1, i tried "limit 1" but they all get limited to 1 both bids n auctions, how do i limit only auctions to 1
how do i go about this, thanks.

$query = "SELECT b.*, u.nick, a.id, a.team1, a.team2 FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "auctions a ON (a.id = b.auction)
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);

To avoid duplicates use "SELECT DISTINCT"

^^even distinct will fail in some certain left join cases.

i simply used column = column group by column this will limit only that column

^^^very good