Author |
Topic |
lithgowers
Starting Member
5 Posts |
Posted - 2012-02-10 : 13:12:36
|
I have a Visual Foxpro database and I am using classic ASP to execute the SQL query below. The idea of the query is to find all bidders in the bidder table for a division (LG) and then find the number of lots that were won (a_lots WHERE salepadnum = salepadnum) AND also how many items they bid on (a_absbid WHERE salepadnum = salepadnum). When I run either one or the other the JOINS work fine however as soon as I add the second LEFT JOIN, the numbers don't add up to what is in the database. Thanks in advance for any help, query and example below. Example: Actual Values: SMITH, JOHN - Bids 7 - Wins 3 With one or the other JOIN: Bids 7 OR Wins 3With both JOINS: Bids 7 - Wins 6 SQL = "SELECT DISTINCT a_bidder.cust_id, a_bidder.company_name, a_bidder.address, a_bidder.city,"_& " a_bidder.region, a_bidder.postal_code, a_bidder.phone, a_bidder.salepadnum,"_& " COUNT(a_lots.sequence) AS lot_count,"_& " COUNT(a_absbid.sequence) AS bid_count"_& " FROM a_bidder"_& " LEFT JOIN a_lots ON a_bidder.salepadnum = a_lots.salepadnum"_ & " LEFT JOIN a_absbid ON a_bidder.salepadnum = a_absbid.salepadnum" SQL = SQL & " WHERE "SQL = SQL & "(a_bidder.salename LIKE '%L&G%' OR a_bidder.salename LIKE '%LG%' OR a_bidder.salename LIKE '%L/G%') "SQLo = SQLo & " GROUP BY a_bidder.cust_id " SQLo = SQLo & " ORDER BY a_bidder.company_name ASC " |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-02-10 : 13:44:13
|
I don't know foxpro, but it looks like you need a distinct count on the a_lots.sequence. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-10 : 15:46:00
|
unless you show some data from tables, hard to see where count is getting messed up. Probably you can post some sample data from them and explain what you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
lithgowers
Starting Member
5 Posts |
Posted - 2012-02-13 : 11:41:16
|
a_bidder.tbl - name - salepadnum - salenum - CustID j. smith - 1290088 - 129 - 120029r. ryan - 1290025 - 129 - 10056 k. kline - 1290055 - 129 - 110284 k. kline – 1550095 - 155 - 110284 k. kline - 16500105 - 165 - 110284k. kline - 18500125 - 185 - 110284 a_absbid.tbl - name - salepadnum - salenum - sequence(lot bidding on) - salelotnum (lot in a sale)j. smith - 1290088 - 129 - 1001 - 129001001r. ryan - 1290025 - 129 - 1001 - 129001001r. ryan - 1290025 - 129 - 1003 - 129001003r. ryan - 1290025 - 129 - 1022 - 129001022k. kline - 1290055 - 129 - 1230 - 129001230k. kline - 1550095 - 155 - 2010 - 165002010k. kline - 16500105 - 165 - 3345 - 185003345a_lots.tbl - salepadnum(winner of the lot) - salenum - sequence(lot_name) - price1290025 - 129 - 1003 - 15000.0016500105 - 165 - 3345 - 1500.001550095 - 155 - 2010 - 2500.0018500125 - 185 - 1555 - 5500.0018500125 – 185 – 1655 – 2500.00So what I am looking for is a) How many absentee bids a user had across all salenums (a_absbid)b) how many lots a user has won across all salenums (a_lots) - an absentee bid may or may not be present for a lot if the bidder is actual in attendance and winsc) grouped by custIDExample from above:- name - custID - bid - wonj.smith - 120029 - 1 - 0r.ryan - 10056 - 3 - 1k.kline - 3 - 4Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-13 : 11:52:39
|
[code]SELECT b.custID,b.name,COUNT(ab.sequence) AS bid,COUNT(l.sequence) AS wonFROM a_bidder bleft join a_absbid abon ab.name = b.nameand ab.salepadnum = b.salepadnumand ab.salenum = b.salenumleft join a_lots lon l.salepadnum = b.salepadnumand l.salenum = b.salenumGROUP BY b.custID,b.name [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
lithgowers
Starting Member
5 Posts |
Posted - 2012-02-13 : 11:55:30
|
quote: Originally posted by Lamprey I don't know foxpro, but it looks like you need a distinct count on the a_lots.sequence.
I added the Distinct to the first a_lot.sequence and it is funny because it works for SOME users but not others... Name - Bids - WinsJ. Smith - 7 - 3 <-- CorrectD. Duck - 6 - 2 <-- Incorrect(should be 3 - 2) |
|
|
lithgowers
Starting Member
5 Posts |
Posted - 2012-02-13 : 13:17:12
|
quote: Originally posted by visakh16
SELECT b.custID,b.name,COUNT(ab.sequence) AS bid,COUNT(l.sequence) AS wonFROM a_bidder bleft join a_absbid abon ab.name = b.nameand ab.salepadnum = b.salepadnumand ab.salenum = b.salenumleft join a_lots lon l.salepadnum = b.salepadnumand l.salenum = b.salenumGROUP BY b.custID,b.name Should this just be "left join a_absbid" without the ab at the end?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-13 : 13:40:07
|
nope ab is the alias so you need it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
lithgowers
Starting Member
5 Posts |
Posted - 2012-02-13 : 15:39:39
|
quote: Originally posted by visakh16 nope ab is the alias so you need it
Gotcha. Unfortunately it still isn't working correctly for some users. It has to be a data problem I would think.Here is how it plays out with different configurations. COUNT(DISTINCT LT.sequence) | Name | Bids | Win || J.Smith | 7 | 3 | <-- correct| K.Hinds | 27 | 4 |COUNT(DISTINCT AB.sequence)| Name | Bids | Win || J.Smith | 7 | 6 || K.Hinds | 23 | 14 |No DISTINCT COUNTS| Name | Bids | Win || J.Smith | 7 | 6 || K.Hinds | 27 | 14 |REAL NUMBERS THAT SHOULD SHOW| Name | Bids | Win || J.Smith | 7 | 3 || K.Hinds | 23 | 4 |This is the query with the table ALIAS.SQL = "SELECT B.cust_id, B.company_name, B.salepadnum, "_& " COUNT(AB.salelotnum) AS bid_count, "_& " COUNT(LT.salelotnum) AS lot_count "_& " FROM a_bidder B"_& " LEFT JOIN a_absbid AB ON AB.salepadnum = B.salepadnum AND AB.salenum = B.salenum AND AB.company_name = B.company_name" _& " LEFT JOIN a_lots LT ON LT.salepadnum = B.salepadnum AND LT.salenum = B.salenum " SQL = SQL & " WHERE "SQL = SQL & "(B.salename LIKE '%L&G%' OR B.salename LIKE '%LG %' OR B.salename LIKE '%L/G%') "SQLo = SQLo & " GROUP BY B.cust_id, B.company_name " SQLo = SQLo & " ORDER BY B.company_name ASC " |
|
|
|