SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple SQL Left Joins COUNT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lithgowers
Starting Member

5 Posts

Posted - 02/10/2012 :  13:12:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/10/2012 :  13:44:13  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/10/2012 :  15:46:00  Show Profile  Reply with Quote
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 - 02/13/2012 :  11:41:16  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2012 :  11:52:39  Show Profile  Reply with Quote

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 


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


Edited by - visakh16 on 02/13/2012 11:52:54
Go to Top of Page

lithgowers
Starting Member

5 Posts

Posted - 02/13/2012 :  11:55:30  Show Profile  Reply with Quote
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 - 02/13/2012 :  13:17:12  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2012 :  13:40:07  Show Profile  Reply with Quote
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 - 02/13/2012 :  15:39:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000