locked
PLEASE HELP - IMPORTANT! RRS feed

  • Question

  • User-1382820488 posted

    I am not entierly sure how to explain this - I am guessing it is referred to as Nested SQL statements.

     Basically if you first picture it like this - I have a table within an Access database called "Job_Atts" that is used for referrencing

    SO for example - in the table i have Job_ID, AttCode, AttDescription

     

    I then have another table within the database called "Jobs", This has Job_ID, Job_Title, Salary.

     

    What I am looking to do is - say first of all

    SELECT Job_ID FROM Job_Atts  WHERE JobCode = '691'

    I then want to take the results from the above query and pick out the Job_Title and Salary from the Jobs Table where the Job_ID from the above query is the same as the Job_ID in the Jobs Table.

    So all in all what the system will dsiplay all the jobs details (Job_Title and Salary) from the Jobs table where the AttCode which is stored in a seperate table is equal to 338.

     

    My way of thinking was to have 2 SQL statements thats nested and some how store the results from the first query into a variable (lets call it result) then use it within the second query

    SO id do something like:

    result = (SELECT Job_ID FROM Job_Atts WHERE Job_Atts = '691' )

                       SELECT Job_Title, Job_Salary FROM Jobs WHERE Job_ID = result

     

    Is it possible to do this??? or is there a simpler way to do it?

     

    Please Please help!

    Thursday, February 5, 2009 7:56 AM

Answers

  • User-1382820488 posted

     Not to worry now mate - I have just this second solved it.

     

    Basically how I did it was like this:

     I opened the Access Database in outlook - Created a direct relationship between the Jobs table and the Job_Atts table using the Job_ID as the key field.

     

    I then simply coded my website to create an SQL statement that reads the following:

    SELECT     Jobs.Job_Title, Salary
    FROM         (Jobs INNER JOIN
                          Job_Atts ON Jobs.JobID = Job_Atts.JobID)
    WHERE     (Job_Atts.AttCode = '691')

    So basically this creates a join between the tables and matches the Job_ID of Jobs to the Job_ID of the Atts table.

    Well pleased with that - Had a burst of knowledge lol

     

    Thanks for the very quick response though

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 8:29 AM
  • User1759999623 posted

    When you create a DropDownList control, you define Text and Value properties for each ListItem.  The Text property is the text displayed for the user to see while the Value property is the value that you want to have associated with the ListItem for the purposes of using the Value in queries.

    If you have a table with these elements, then you could construct a DropDownList control using a SqlDataSource associated with this particular table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 10:54 PM

All replies

  • User1759999623 posted

    What information do you want to show?  Which fields are from which tables? What is the relationship between the tables (foreign key, plus one-to-one, one-to-many, etc.)?

    Thursday, February 5, 2009 8:22 AM
  • User-1382820488 posted

     Not to worry now mate - I have just this second solved it.

     

    Basically how I did it was like this:

     I opened the Access Database in outlook - Created a direct relationship between the Jobs table and the Job_Atts table using the Job_ID as the key field.

     

    I then simply coded my website to create an SQL statement that reads the following:

    SELECT     Jobs.Job_Title, Salary
    FROM         (Jobs INNER JOIN
                          Job_Atts ON Jobs.JobID = Job_Atts.JobID)
    WHERE     (Job_Atts.AttCode = '691')

    So basically this creates a join between the tables and matches the Job_ID of Jobs to the Job_ID of the Atts table.

    Well pleased with that - Had a burst of knowledge lol

     

    Thanks for the very quick response though

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 8:29 AM
  • User-1382820488 posted

     What i do need help with however is tweaking the codes WHERE clause to be dependant on a drop down box - so in the drop down box if i have a list of values such as:

    691
    500
    235

    If the user clicks on 691 it draws out the jobs with the AttCode of 691

    then if the user clicks 500 it draws out the jobs with AttCode of 235. and so on.

     

    Ideally what id love to do is give each value a name rather than a number - as well as an ID

    So it would be

    Web Development = 691

    Web Design = 500

    Graphics = 235

     

    So the drop down box reads:

    Web Development
    Web Design
    Graphics

    yet if you select web development is searchs the database WHERE AttCode = 691.

    Thursday, February 5, 2009 8:36 AM
  • User1759999623 posted

    When you create a DropDownList control, you define Text and Value properties for each ListItem.  The Text property is the text displayed for the user to see while the Value property is the value that you want to have associated with the ListItem for the purposes of using the Value in queries.

    If you have a table with these elements, then you could construct a DropDownList control using a SqlDataSource associated with this particular table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 10:54 PM