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 |
|
a.ashabi
Posting Yak Master
117 Posts |
 Posted - 2008-08-07 : 04:36:00
|
| Hi.I have this query:SELECT distinct c.cat_level2_idFROM dbo.tbl_configurator c LEFT OUTER JOIN dbo.tbl_cat_relation r ON r.cat_level2_id = c.cat_level2_idWHERE (r.cat_level2_id IS NULL)which works fine.but i also need to select other fields(cat_level2_id,cat_level3_id)from dbo.tbl_configurator.if I delete the DITINCT or add cat_level2_id,cat_level3_id to the query,get the wrong result.The DISTINCT should work only for c.cat_level2_id,how can I select other fields without changing the results?thanks |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-07 : 04:37:52
|
| sounds like you want to use GROUP BY rather than a DISTINCT. show some sample data with an example of which records to do / con't want to select and it will be easier to help youEm |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-07 : 04:40:38
|
quote:
Originally posted by a.ashabi
Hi.I have this query:SELECT distinct c.cat_level2_idFROM dbo.tbl_configurator c LEFT OUTER JOIN dbo.tbl_cat_relation r ON r.cat_level2_id = c.cat_level2_idWHERE (r.cat_level2_id IS NULL)which works fine.but i also need to select other fields(cat_level2_id,cat_level3_id)from dbo.tbl_configurator.if I delete the DITINCT or add cat_level2_id,cat_level3_id to the query,get the wrong result.The DISTINCT should work only for c.cat_level2_id,how can I select other fields without changing the results?thanks
use min or max for others. but please note that you will only a single value for each cat_level2_id with thisSELECT c.cat_level2_id,{MIN/MAX}(cat_level2_id),{MIN/MAX}(cat_level3_id)FROM dbo.tbl_configurator c LEFT OUTER JOIN dbo.tbl_cat_relation r ON r.cat_level2_id = c.cat_level2_idWHERE (r.cat_level2_id IS NULL)GROUP BY c.cat_level2_id |
|
|
|
a.ashabi
Posting Yak Master
117 Posts |
Posted - 2008-08-07 : 05:05:21
|
| thank u visakh16.it worked :) |
|
|
|
|
|
|
Distinct