Last post Feb 05, 2009 10:54 PM by Careed
Feb 05, 2009 07:56 AM|idex_rob|LINK
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!
Feb 05, 2009 08:22 AM|Careed|LINK
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.)?
Feb 05, 2009 08:29 AM|idex_rob|LINK
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
Feb 05, 2009 08:36 AM|idex_rob|LINK
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:
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:
yet if you select web development is searchs the database WHERE AttCode = 691.
Feb 05, 2009 10:54 PM|Careed|LINK
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.