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 |
|
dba123
Yak Posting Veteran
90 Posts |
 Posted - 2008-11-10 : 23:34:18
|
| I am wondering why I had to add distinct to this query below to get the same results as query 1 with just the fact that I added an extra field that lied in the table I joined toQuery 1:select quantity, price, cost, saleprice from Pricing where itemID = 440851 order by quantityYields the following example results1, 2.00, 1.50, 1.001, 1.00, .50, .25Query 2:select p.qty, i.message, p.price, p.cost, p.salepricefrom Pricing pinner join order o on o.itemID = p.itemIDwhere p.itemID = 440851 order by p.quantityYields more than 200 results. Not sure whyQuery 3:select distinct p.qty, i.message, p.price, p.cost, p.salepricefrom Pricing pinner join order o on o.itemID = p.itemIDwhere p.itemID = 440851 order by p.quantityYields the correct results. Same as Query 1 but with the added i.message column.So whenever I add distinct it just makes me feel uncomfortable. Like it's a quick fix or something. So in this case I'm trying to understand why the inner join brought back a ton of rows and is close to Query 3 which yielded just 2 rows like Query 1 did. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-10 : 23:48:30
|
| thats because you've 1 to many relation existing between order and pricing table. so join will bring you more rows. when you take distinct it will only retrieve distinct field value which is why its same as query one.One think i didnt understand is where did you get 1,message from? i cant see a table which is being aliased i in query. so i dont think this will work. it should throw an error. |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-10 : 23:49:50
|
| What is i.message and from which table it belongs ? |
|
|
|
|
|
|
|
|
Adding Distinct