Select single distinct field over multi-field selection.

Hi all

I am having trouble with retrieving information from my SQL database in the way I would like it.

The select statement has to retrieve data from 5 different tables, only certain fields have to be returned. One table contains a category name, a single category can appear more than once in this table, I wish only to retrieve the category name once. When I use “DISTINCT” I get errors due to the nature of the data in the other tables, such as the category description.

Basically what I wish to achieve is to display the category name (once) out of the category table along with the description.

Can anyone shed some light on this or provide me with a solution.

I have use GROUP BY, but this also results in errors

 

Thanx!!!

 

0
JdeBruin
1/27/2009 9:25:41 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
234 Views

Similar Articles

[PageSpeed] 44

 It would be better if you post the table structure and your query.

0
anonymouswrites
1/27/2009 11:23:40 AM

Due to the nature of the data I am not allowed to show any such information where it may be seen. I do appologies.

I

0
JdeBruin
1/27/2009 1:51:34 PM

If you want to present your data like this

some info Category1

another info 

third info   Category2

 

then it's not easy to achieve with SQL Server and may be better done in the presentation layer.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
1/27/2009 2:02:19 PM

JdeBruin:

a single category can appear more than once in this table, I wish only to retrieve the category name once.

 

 

Are you joining to this table based on an ID? Since you can't show any sample data, or even table layouts, I will.  Based on your description of the problem, I'll guess that your problem can be solved by using derived tables.  Basically, as derived table will allow you to select distinct from it, while not affecting the other parts of your query.

 In the sample code I show below, I show a query that simply returns all the data.  The second query has a distinct in the main query, and the 3rd query has distinct on just the @Category table.  Notice that the 2nd and 3rd queries return exactly the same data.

 

Declare @Category Table(Id Int, Category VarChar(20))

Insert Into @Category Values(1,'Blue')
Insert Into @Category Values(1,'Blue')
Insert Into @Category Values(1,'Blue')
Insert Into @Category Values(2,'Blue')
Insert Into @Category Values(2,'Red')

Declare @Temp Table (Id Int, Data VarChar(20))

Insert Into @Temp Values(1,'Apple')
Insert Into @Temp Values(2,'Banana')


Select * 
From   @Temp T
       Inner Join @Category C
          On T.Id = C.Id

Select Distinct T.Id, T.Data, C.Category
From   @Temp T
       Inner Join @Category C
          On T.Id = C.Id

Select T.Id, T.Data, AliasName.Category
From   @Temp T
       Inner Join (
          Select Distinct Id, Category
          From   @Category 
          ) As AliasName
          On T.Id = AliasName.Id
  

 You can copy/paste the code to a query window to see how this works.  Hope it helps.


-George
0
gmmastros
1/27/2009 2:08:58 PM

 

Thanx for the help. It works!!!

You saved me from pulling out my hair!!

It is creatly apprecaited!!!

 

 

 

0
JdeBruin
1/27/2009 3:01:44 PM

You can mark the correct response with an answer.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
1/27/2009 6:27:28 PM
Reply:

Similar Artilces:

Selecting value in one field then causes value in another field to be selected.
I have a web form which contains a date field, the date is populated using a DatePicker which is Javascript based. The form also has a drop down box containing 3 values. What I want to do is when a date has been selected that this will cause the dropdown list value to change to dated which is one of its 3 values. i.e. when the date field is not blank then the value will be selected in the dropdown. What I also want to happen is when the date has been selected and the dropdown value is dated then the date field and the dropdown field will be set to read only.Anyone any ideas how to this usin...

problem with selected text field and selected value field in check box list
Hi! i have a  problem with check box list:here is the code for binding the check box list with sqldatasource  SqlDataSource1.SelectCommand = "select vcentrolno,inttpaperno from tabmarksdetails where vcbunchno='" & strBunchNo & "'"  chkEnrolNos.DataBind()say now my chkEnrolNos consist of data in this way:vcentrolno        inttpaperno selected text   selected valueA11439            1A11439            2AR439    ...

Only select few fields to gridview when clicking some fields on gridview remaining fields should be in a popup window
Pls help me out this. I am having a table of 30 fields.. i dont want to show everyfields in the gridview that would be very messy. i need to show only 2 or 3 fields in the gridview and if the user click on any of the field in the gridview the 30 fields should be shown in a small size window....  And the small size window should have a button(e.g: approved) to update a field(approved field : no to yes) in the table...... Is there any possibilities to do this..if so pls guide me... Thanks in advance Hi muthulakshmanan , Add some boundfield to GV, which used to show only few co...

Select three fields in SQL datasource but only display two in datalist
How would I do this?  I have to select the First and last name to show up in the datalist, but I also need to select the key because I am updating another column in the selected row.  How would I display only the first and last name? You can edit the html in the aspx page and remove that item from the templates.BrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. The problem is the template doesnt know the name yet because the sql select is selecting from a "' + table + '" so that wouldnt work.  Sorry I forgo...

Select Multiple Fields from a DropDownList Selection
I have a database that contains these fields ‘EmployeeID’, ‘EmployeeName’ and ‘EmailAddress’.   I use the ‘EmployeeID’ and ‘EmployeeName’ fields in a DropDownList inside a FormView control to insert records.  In other words a user selects the EmployeeName field and the EmployeeID field is posted to the database.   I would like to know how I can also query and select the EmailAddress field from the database when the EmployeeName is selected by the form user.  I need to be able to use the EmailAddress to send an email to the appropriate employee. ( I already know how to...

select two fields into one field
hi i want to select two fields and put them into one field. for example, i want to select firstname and surname and then put them into a field called name in a new table. does anybody have any suggestions how i can do this? i want to use a 'select into' statement becasue i am gathereing fields from different tables and putting them into one  thanks You should keep them separate. You can always link them together in your SELECT statement: SELECT firstname + ' ' + surname as name FROM yourtableLimno thanks for your reply. i cant keep them seperate bec...

Disable some fields based on a field selection
Hi, I was unable to find a learning tutorial on this. please suggestion on following issue... If CheckBox1 selected Yes (two options Yes/No), then TextBox1 & TextBox2 should be enabled, otherwise, they both should be disabled to enter any data. Please suggest.  I see that we can disable a text field, but not able to locate the conidtional parameter. thanks ldpsekhar ldpsekhar: If CheckBox1 selected Yes (two options Yes/No), then TextBox1 & TextBox2 should be enabled, otherwise, they both should be disabled to enter any data. Please suggest.  if (CheckBox1....

select * vs select field, which one is faster ?
Suppose we have a table of 50 fields, which command should we use to take the result faster : SELECT * FROM Employee SELECT employee.id, employee.name FROM Employee Thanks for help. -- Best regards, Ridwan Goldbase Technology Visit our Homepage : http://www.geocities.com/Wallstreet/Floor/5251/index.html http://members.tripod.com/~goldbase http://members.xoom.com/goldbase * Check out the Y2K (Year 2000 Problems - Millenium Bugs) The second one. In that case, only data for two columns is retrieved. -- - Eric Aling [TeamPS], Cypres Informatisering bv, T...

SELECT DISTINCT and TEXT field :(
--------------2506CE24BE7A87456410715C Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: base64 Hi everybody, I'm doing a JOIN that would need a DISTINCT cause I've got some duplicates. The problem is that one of the fields I have to show is a TEXT field. How could I solve this problem? My query is like: select distinct annonces.*,Format=convert(char(10),annonces.date_vente,103),TGI.nom,avocats.nom from annonces,TGI,avocats where dep_id<1000 Thanks for your help Daniel -- Daniel Garcia Administration Syst�me Anakine Communications ...

Multi-select custom fields
Hi, I am a Bugzilla administrator and would like to define custom fields with multi-select options (just like the CC field, in which it is possible to select multiple names and not just one). For example - if my custom field is called "Affected Clients" and has the options "Client1", "Client2" & "Client3", I would like to select both "Client1" and "Client3". Is it possible? Thanks, David Quoting David Zisner: > Hi, > I am a Bugzilla administrator and would like to define custom fields > with multi...

display the selection of a single field
Good morning! Does anyone know how to select a column or better a field in a DataWindow containing a Cross Table? I can select a row with dw.selectrow(row, true). But I can't find a way to display the selection of a single field. Thank you for your help susanne <John_Vanleeuwe> wrote: > How about this line in the itemfocuschanged event ? > > SelectText( 1, Len( GetText() ) ) That does only work in editable fields. But all fields in a Cross Table are noneditable. How about this line in the itemfocuschanged event ? SelectText( 1, Len( GetText...

Select Distinct on One Field
I am trying to write a Sybase ASE SQL query that returns many fields but only has one instance of each value in one of the fields. For example, suppose I have the following data: ID | Date | Provider ----------------------- 1 1/9/2009 | SupplierB 1 1/10/2009 | SupplierA 2 1/9/2009 | SupplierC I want one of the rows with ID 1 returned and the row with ID 2 returned. Although I don't care which row with ID = 1 is returned, I do want everything in that corresponding row to be returned (in other words, no mixing and matching of data from multiple rows like a min o...

SELECT DISTINCT and TEXT field :(
--------------7594190AC99423D335FEF61C Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: base64 Hi everybody, I'm doing a JOIN that would need a DISTINCT cause I've got some duplicates. The problem is that one of the fields I have to show is a TEXT field. How could I solve this problem? My query is like: select distinct annonces.*,Format=convert(char(10),annonces.date_vente,103),TGI.nom,avocats.nom from annonces,TGI,avocats where dep_id<1000 Thanks for your help Daniel -- Daniel Garcia Administration Syst�me Anakine Communications ...

Select distinct field from a DataSet
I need to select distinct field from a data set and store them on ArrayList Anybody can help me with coming up with the code. I can also do it with LINQ if anybody knows how. Thank you.  Try this blog post:http://weblogs.asp.net/eporter/archive/2005/02/10/370548.aspx Darrell Norton, MVPDarrell Norton's BlogPlease mark this post as answered if it helped you! Thanks for you help.I have a different quesiton. var dataSource = DB.Trades; store it to a variable and than pass it to my query which is as follows:   from t in dataSource    ...

Web resources about - Select single distinct field over multi-field selection. - asp.net.sql-datasource

Multi-Sport Field - Wikipedia, the free encyclopedia
Multi-Sport Field is a 2,500-seat multi-purpose stadium in Washington, D.C. on the campus of Georgetown University . The field was originally ...

Total E&P UK Limited Awards CHC Helicopter Long-Term, Multi-Field North Sea Helicopter-Services Contract ...
TheBlaze is a news, information and opinion site brought to you by a dedicated team of writers, journalists & video producers. Our goal is to ...

ZeroTouch 'optical multi-touch force field' makes a touchscreen out of just about anything
The rise of tablets and smartphones has made the touchscreen a rather ubiquitous interface, but they aren't everywhere quite yet. A group of ...

Saints vs. Jaguars Multi-Lateral TD/Missed Field Goal - 12/21/03 - NFL Videos
The destination for all National Football League-related video on the web. Game and player highlights, news stories, team and player-related ...


Multi-purpose sensor measures temperature, vibrations and electric fields with high spatial resolution ...
Glass fibres can do more than transport data. A special type of glass fibre can also be used as a high-precision multi-purpose sensor, as researchers ...

Multi-configurational self-consistent field - Wikipedia, the free encyclopedia
Multi-configurational self-consistent field (MCSCF) is a method in quantum chemistry used to generate qualitatively correct reference states ...

Resources last updated: 12/16/2015 11:01:27 AM