|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select all Rows on distinct only on one columnI've been racking my brain for days on how to select all the details on a distinct field of only on one column. For Eg. TableOne ------------------------------ ProdID | Description ------------------------------ 1 | Toy 1 | Toys 1 | Xmas Toys 2 | Lollies 2 | Candy TableTwo -------------------------------- ProdID | Price -------------------------------- 1 | $25 2 | $90 Result ---------------------------------------------------- ProdID | Description | Price ---------------------------------------------------- 1 | Toys | $25 2 | Lollies | $90 Above is ultimately what I want the join to become 1) "Group" all the items from Table 1, if they have the same product ID (even if the description is not the exact same) consider them the same item 2) Join that distinct list of products from Table 1 with the pricing from Table 2 and display the final output. If anyone has an idea, please post.... Thanks > 1 | Toys | $25 How did you come up with this result? Why "Toys" and not "Toy" or "Xmas Toys"? Well actaully you are right. I dont actually care which description is
there, becuase I will be able to interpret them after I get ther result In that example I just put the first one listed there thats all Aaron Bertrand [SQL Server MVP] wrote: Show quote > > 1 | Toys | $25 > > How did you come up with this result? Why "Toys" and not "Toy" or "Xmas > Toys"? If you don't care, then do something simple like:
SELECT a.ProdID, a.ProdDescription, b.Price FROM (SELECT ProdID, MIN(ProdDescription) as ProdDescription FROM Table1 GROUP BY ProdID) a JOIN Table2 b ON a.ProdID = b.ProdID ORDER BY a.ProdID HTH, Stuj ason.t***@gmail.com wrote: Show quote > Well actaully you are right. I dont actually care which description is > there, becuase I will be able to interpret them after I get ther result > > In that example I just put the first one listed there thats all > > > Aaron Bertrand [SQL Server MVP] wrote: > > > 1 | Toys | $25 > > > > How did you come up with this result? Why "Toys" and not "Toy" or "Xmas > > Toys"? Another way would be to redesign the schema to add another table for the
alternate descriptions. Might seem something that will be more complicated than your present schema but in fact, this will give much more simpler queries. Show quote "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1149732590.909173.205850@u72g2000cwu.googlegroups.com... > > If you don't care, then do something simple like: > > SELECT a.ProdID, a.ProdDescription, b.Price > FROM (SELECT ProdID, MIN(ProdDescription) as ProdDescription > FROM Table1 > GROUP BY ProdID) a JOIN Table2 b ON a.ProdID = b.ProdID > ORDER BY a.ProdID > > HTH, > Stuj > > ason.t***@gmail.com wrote: >> Well actaully you are right. I dont actually care which description is >> there, becuase I will be able to interpret them after I get ther result >> >> In that example I just put the first one listed there thats all >> >> >> Aaron Bertrand [SQL Server MVP] wrote: >> > > 1 | Toys | $25 >> > >> > How did you come up with this result? Why "Toys" and not "Toy" or "Xmas >> > Toys"? >  |
|||||||||||||||||||||||
Other interesting topics