|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
August 8th, 2006, 10:35 AM
|
|||
|
|||
|
SQL count, condition from seperate table
select contact1.key1, contact2.umksalreg count(umksalreg) from contact1, contact2 where contact1.key1 = 'purchaser' and contact1.accountno=contact2.accountno group by contact2.umksalreg
Hopefully you get what I'm trying to do. I keep getting an incorrect syntax error. I'm counting the number of entries with 'purchaser' in the key1 column in the contact1 table, and grouping them by umksalreg which resides in the contact2 table. I'm a relative SQL newbie, and feel kind of out of my depth. please be gentle! Jho.
|
|
#2
August 14th, 2006, 06:23 AM
|
|||
|
|||
|
Well I figured it out in the end, not sure if I'm using good SQL practise, but it gives and accurate count and doesn't give any errors.
For anyone else who wants to know how, the code is below. I used a left join, and our database frontend decided to throw the 'nolock' in. Code:
SELECT COUNT(*) AS NUMBER, UMKSALREG FROM CONTACT1 LEFT JOIN CONTACT2 WITH (NOLOCK) ON (CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO) WHERE CONTACT1.KEY1='PURCHASER' GROUP BY CONTACT2.UMKSALREG
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > SQL count, condition from seperate table |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
