How to select value from another select statement?

Table People (column - Id,Count)

Table Skill (column - Id,Skill)

first I want to select people who have Skill in "2"

select a.Id

 from people a, Skill b

where a.Id = b.Id and b.skill=2

Result 1 :

 Id

---

aa

bb

cc

dd

ee

 

and then I want to select which people have a Min(count) from Result 1 how to write a query? combine 2 query into 1 query? thank you

 

P.S. I'am talking about MSSQL 2000

thank you very much

0
jcjcjc
9/5/2007 7:37:10 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
534 Views

Similar Articles

[PageSpeed] 23

The relationship would be clearer if

Table People (column - Id,Count)
Table Skill (column - Id,Skill)

Became
Table People (column - Id,Count)
Table Skill (column - Id,PersonId, Skill)


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
9/5/2007 9:20:33 AM

select a.Id,min(a.count)[count]

from people a, Skill b

where a.Id = b.Id and b.skill='2'

group by a.id order by a.id desc

or

select top 1 a.Id,min(a.count)[count]

from people a, Skill b

where a.Id = b.Id and b.skill='2'

group by a.id order by a.id desc


Thanks,
Mahadeo

-----------------------------------------------
Best is Not the END point, but a starting point for Innovation
------------------------------------------------
Remember to mark posts as the "Answer" to help future users.
0
Mahadeomatre
9/5/2007 9:23:01 AM
Reply:

Similar Artilces:

Select Statement, within another Select Statement
Hey All, I have have 2 tables, one is a parrent table called album, the second table is called album content. For each album there will be several album content records I am trying to do a select statement that for each album displayed a single or TOP(1) record only is displayed from the album content table. Now for added complexity not all albums may have album content records, so I have to do an Outer Join to get the album information to show even if there are no album content records. I am using the wizard in VWDE 2008 becuase I am not great at SQL, here is what I have so far, and...

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...

select cheboxlist if any values are selected in sql
hello and thanks in advance,  I have a checkboxlist that is created dynamically from table1 once the records are inserted they are inserted in table2. i can save the values into the table and remove them if i uncheckd them that is working fine. the problem is to checked the items that have been selected already, how to i do this?? really really confuse...   here is the cs code; namespace IMS_2K9.Admin { public partial class Order_Charges : System.Web.UI.Page {private string connectionString = WebConfigurationManager.ConnectionStrings["IMSCon...

Select from another select statement to a repeater
Hi,I have a big problem, I hope you can help me with.What I want:A forum where there is 3 columns Subject, AnswersCount, LastAnswerI have one table with all the data. And two selects functions, where I on the first, get all Subjects for all questions, and on the second select get the Count for all answers on a specific question (with input quiestionId). Both functions works perfect, when I use them seperatly.But I have to go through all rows in the first selection to find the Count What shall I do to that?The code I have now is:The Repeater, where I want to put all data into:<HeaderTempla...

select in the select statement
Hi, I am trying to add another select statement in my original/outer/overall select statement. Something like: Select 1, 2, 3 = (Select # from Table1 Where....) From Table 2 Where.... Does anyone know the standard way to write this or is there any other alternative that can perform the same behavior. Much thanks!! Kenny That's database dependent. In oracle you would use: select A.* from ( select * from mytable) A -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. MySybase http://my.sybase.com/mysybase "Kenny Tran" <...

Sql Datasource select statement
I have a gridview that uses a sqldatasource to display (classid, userid, startdate, enddate) from a class table.  My datagrid also contains a submit button.  When this button is clicked I am inserting this data into a new table called register.  I need a way to not show classes for the user if that user is already in the register table for that class.  Currently all items are being shown which would allow the user to register for the same class more than once.  Any ideas?   Thanks  you could make classid and userid as a pri...

selected Listbox values in SQL statement
I have seen some information on this but have not understood fully the answers.  I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer.  How do I get selected values from a LIstbox into a SQL statement using the IN command   First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items  If li.Selected = True Then    strPC += li.Value & ", "  End IfNextLabel5.Text = strPC The re...

SQL select statement to select the last ten records posted
SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered. Doesnt what you have work? ***********************Dinakar NethiLife is short. Enjoy it.*********************** Yeah, I was comparing test data to production, my bad. Thanks!...

SELECT from SELECT
Hi! I remember seeing an example where you can do a select from a nested select, but I cannot seem to make it work. So it would be something like this: select max( name ) from ( select name from customer union select name from vendor ) Was I dreaming I saw that? Thanks! D In article <4500243f@forums-2-dub>, daniel_AT_infrontsoftware_DOT_com@N0SPAM.com says... > Hi! > > I remember seeing an example where you can do a select from a nested > select, but I cannot seem to make it work. No, you weren't. Try (UNTESTED!) adding a...

Using selected values from a Multi Select Listbox in a SQL query
I have seen a few post concerning this but don't really understand fully as I am new to this.  I would appreciate any help 1) First I loop through the Listbox control and get the selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End If Next Label5.Text = strPC The resulting string looks something like: 101, 102, 100 ,    I get rid of the last space and "," with the following statement Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) 2) I would then like to ...

Dropdownlist selected value passes first value in list no matter what value is selected. why ?
 HI . I have a dropdownlist. Databinds ok but when I must pass a value as a Selected value it always passes the first value in the list even if I select another value. I tried to set the ddl to enableviewstate false but it doesn't change anything. Any ideas ? Code below, thanksLoading the ddl:       productCategories = CatalogAdmin.Admin_Product_Categories_NotIn(productId);        for (int i = 0; i < productCategories.Rows.Count; i++)        {       &...

Accessing the selected value from a Select
Need to access the selected value from a Select in client script.  I can get the selectedIndex but where do I find the selected value? Will this give it to me? form1.Select1.Options(form1.Select1.SelectedIndex)   form1.Select1.options[form1.Select1.selectedIndex] Try form1.Select1.options[form1.Select1.selectedIndex].value> penny for a thought...

DROPDOWNLIST DOES NOT SELECT THE SELECTED VALUE
Hi all, i am using C# , asp.net vs2005. ajax 1.0 i am populating data programitically . when i select first value, dropdown control select the same. when i select second value ,dropdownlist select second value too But i does not continue  from third value onward . when i select third value, dropdownlist control select second value then after behavior reman the same no matter what i select dropdownlist control select second value ONLY.<asp:DropDownList id="Ddlzse" runat="server" Width="165px" Font-Bold="False" Fo...

Select statement joined on (select..)
 Hi,I have some duplicates CarName and CarType combinations in my table (nevertheless the ID, which is identity) is unique.I tried sorting it out with:SELECT * FROM DimProductsJOIN (select distinct CarType,CarName from DimProducts)But that just gives me> Line 2: Incorrect syntax near ')'.Any ideas?  immediate answer would be that you have to name your second table: SELECT * FROM DimProductsJOIN (select distinct CarType,CarName from DimProducts) AS secondTableName ------------------------if an answer, mark it so. What I understand from your question is, you ...

Web resources about - How to select value from another select statement? - asp.net.sql-datasource

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

The innovation nation: Elitist statement which won't generate change
Readers discuss the Turnbull government's innovation statement.

The statements murdered grandmother Helen Dawson Key's family will never get to read
If the family members of Helen Dawson Key had their chance, they would have faced her killer and told of their pain.&nbsp;

Innovation Statement: Tech industry asks Turnbull for more money, please
The government's Innovation Statement on Monday was widely applauded, but did it go far enough?

Madonna says Sean Penn never assaulted her in court statement, denying previous reports
... of the allegations that have surfaced over the years accusing Sean of incidents of physical assault and abuse against me," she said in a statement ...

FOMC Statement: Fed Funds Rate target range increased to 1/4 to 1/2 percent
FOMC Statement: Information received since the Federal Open Market Committee met in October suggests that economic activity has been expanding ...

What changed in new Fed statement
This is a comparison of today's FOMC statement with the one issued after the Fed's previous policy-making meeting on Oct. 28.

Dallas Stars: Cold facts: Loss to Blues 'clearly a statement' that Stars' upcoming divisional games will ...
Blues 3, Stars 0Click here for the full box score.Three Stars1. David Backes, Blues Goal, assist and fight give St. Louis captain the Gordie... ...

Comic-Con Organizers Issue Statement After Fan Confusion
... desire to expand their annual convention in San Diego. Comic-Con International made the unusual move today of putting out a statement regarding ...

Resources last updated: 12/18/2015 1:47:17 AM