Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple SQL Left Joins COUNT

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 3
With 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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

lithgowers
Starting Member

5 Posts

Posted - 2012-02-13 : 11:41:16
a_bidder.tbl
- name - salepadnum - salenum - CustID
j. smith - 1290088 - 129 - 120029
r. ryan - 1290025 - 129 - 10056
k. kline - 1290055 - 129 - 110284
k. kline – 1550095 - 155 - 110284
k. kline - 16500105 - 165 - 110284
k. kline - 18500125 - 185 - 110284

a_absbid.tbl
- name - salepadnum - salenum - sequence(lot bidding on) - salelotnum (lot in a sale)
j. smith - 1290088 - 129 - 1001 - 129001001
r. ryan - 1290025 - 129 - 1001 - 129001001
r. ryan - 1290025 - 129 - 1003 - 129001003
r. ryan - 1290025 - 129 - 1022 - 129001022
k. kline - 1290055 - 129 - 1230 - 129001230
k. kline - 1550095 - 155 - 2010 - 165002010
k. kline - 16500105 - 165 - 3345 - 185003345

a_lots.tbl
- salepadnum(winner of the lot) - salenum - sequence(lot_name) - price
1290025 - 129 - 1003 - 15000.00
16500105 - 165 - 3345 - 1500.00
1550095 - 155 - 2010 - 2500.00
18500125 - 185 - 1555 - 5500.00
18500125 – 185 – 1655 – 2500.00


So 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 wins
c) grouped by custID

Example from above:
- name - custID - bid - won
j.smith - 120029 - 1 - 0
r.ryan - 10056 - 3 - 1
k.kline - 3 - 4

Thanks in advance.

Go to Top of Page

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 won
FROM a_bidder b
left join a_absbid ab
on ab.name = b.name
and ab.salepadnum = b.salepadnum
and ab.salenum = b.salenum
left join a_lots l
on l.salepadnum = b.salepadnum
and l.salenum = b.salenum
GROUP BY b.custID,b.name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 - Wins
J. Smith - 7 - 3 <-- Correct
D. Duck - 6 - 2 <-- Incorrect(should be 3 - 2)
Go to Top of Page

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 won
FROM a_bidder b
left join a_absbid ab
on ab.name = b.name
and ab.salepadnum = b.salepadnum
and ab.salenum = b.salenum
left join a_lots l
on l.salepadnum = b.salepadnum
and l.salenum = b.salenum
GROUP BY b.custID,b.name



Should this just be "left join a_absbid" without the ab at the end?





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 "


Go to Top of Page
   

- Advertisement -