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.
| Author |
Topic |
|
sqlhelp14
Yak Posting Veteran
55 Posts |
 Posted - 2009-11-30 : 14:51:31
|
| i know its simple for u but i don't get results...i have one sql statement -select codeid, codetitle, codedesc from codebase where codeid = '10A' --getting 7 rowsselect codeid, salesno, salesdesc, quantity from salesbase where codeid = '10A' -- getting 747 rowsselect * from codebase cleft outer join salesbase s on c.codeid = s.codeidwhere c.codeid= '10A'--its giving me 5229 rows..instead of that i want just 7rows as correct results so what will be my query? any suggestions? please..thanks |
|
|
sql-programmers
Posting Yak Master
190 Posts |
Posted - 2009-11-30 : 15:03:05
|
| The first thing that I would try is using an inner join instead of an left outer join. In addition, you may need to use the distinct keyword when selecting the fields from the codebase table.SQL Server Programmers and Consultantshttp://www.sql-programmers.com/ |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-30 : 15:13:41
|
| IF there can be more than one salesno, salesdesc, etc associated with each codeid, you'll return more than 7 records, even with inner join or distinct. What is it you're really after?SELECT cb.codeid, cb.codetitle, cb.codedesc ,s.qtyFROM codebase cbINNER JOIN (select codeid, [qty] = sum(quantity) from salesbase where codeid = '10A' group by codeid) sON cb.codeid = s.codeidJimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
simple query