Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,194,772 members, 7,955,947 topics. Date: Sunday, 22 September 2024 at 07:24 PM |
Nairaland Forum / Science/Technology / Programming / Duplicate Result (1154 Views)
Duplicate Result by skyhighweb(m): 7:45am On May 13, 2017 |
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); |
Re: Duplicate Result by Nobody: 9:10am On May 13, 2017 |
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); |
Re: Duplicate Result by skyhighweb(m): 9:44am On May 13, 2017 |
dhtml18:group by id reduced both to 1 result was hoping to reduce a.id to 1 n let d rest by as they are |
Re: Duplicate Result by Nobody: 8:31pm On May 13, 2017 |
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. |
Re: Duplicate Result by skyhighweb(m): 10:54pm On May 13, 2017 |
dhtml18:already figure it out n d method u said didnt work, sooooooooo. |
Re: Duplicate Result by Nobody: 2:42am On May 14, 2017 |
skyhighweb: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. |
Re: Duplicate Result by skyhighweb(m): 6:55am On May 14, 2017 |
dhtml18:make another seperate query, thus not including left join |
Re: Duplicate Result by Nobody: 7:58am On May 14, 2017 |
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. |
Re: Duplicate Result by skyhighweb(m): 8:26am On May 14, 2017 |
easier said than done |
Re: Duplicate Result by Nobody: 4:41pm On May 14, 2017 |
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. |
Re: Duplicate Result by webdeveloperqx: 11:11pm On May 14, 2017 |
skyhighweb: To avoid duplicates use "SELECT DISTINCT" |
Re: Duplicate Result by Nobody: 1:15am On May 15, 2017 |
^^even distinct will fail in some certain left join cases. |
Re: Duplicate Result by skyhighweb(m): 4:52am On May 15, 2017 |
i simply used column = column group by column this will limit only that column |
Re: Duplicate Result by Nobody: 9:31am On May 15, 2017 |
^^^very good |
(1) (Reply)
Create Your Website From Scratch Here! (tutorials) / Can This Garden Robot That Watches And Water Your Plants Be Used In Nigeria?. / Gain In Attending It Professional Schools Like Aptech, Niit Etc.???
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 20 |