none
Select statement across three tables

    Question

  • I'm unsure of how to right this or if it is even possible.  Here's my situation, I am creating a data entry web site using asp.net and sql server 2005.  On the data entry form the user will select a "Department Unit" from a dropdownlist (this I have working).  A few controls down I need to select a "Code" based of the "Unit Type".  I'm new to the asp.net and have never had to do this before in MS Access or SQL. 

    Tables are as follows:

    Department                      Unit_Data                               Code
    Department_Unit             Unit Name                          Unit_Type
    StartTS                           Unit_Code                          Code
    EndTS                            Unit_Type                           Code Description
    Code

    The Department table is the main data entry table, What I need to do is get the Code from the Code table based of the Unit_Type in the Unit_Data Table.  This all needs to work off the Department_Unit selected value.  How do I right this?

    Any assistance would be greatly welcomed?

    Terry
    Wednesday, March 17, 2010 2:41 PM

Answers

  • Something like

    select D.*, UD.*, C.* from Department D inner join UnitData UD on D.Code = UD.Unit_Code inner join Code C on UD.Unit_Type = C.Unit_Type where D.Department_Unit = @DepartmentUnit

    ?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 17, 2010 2:48 PM
  • Terry,

    I think this is more related to ASP.NET then to T-SQL. If I remember correctly, you can use data bindings in your ASP.NET controls to populate what you need based on values selected in other controls.

    Regards,
    Akim

    Wednesday, March 17, 2010 2:52 PM

All replies

  • Something like

    select D.*, UD.*, C.* from Department D inner join UnitData UD on D.Code = UD.Unit_Code inner join Code C on UD.Unit_Type = C.Unit_Type where D.Department_Unit = @DepartmentUnit

    ?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 17, 2010 2:48 PM
  • Tmac

    to answer you question we need to know how the three tabels are related. You must post their PK and FK references.
    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    Wednesday, March 17, 2010 2:51 PM
  • Terry,

    I think this is more related to ASP.NET then to T-SQL. If I remember correctly, you can use data bindings in your ASP.NET controls to populate what you need based on values selected in other controls.

    Regards,
    Akim

    Wednesday, March 17, 2010 2:52 PM
  • Hi,

    You are right Akim,

    There is a property called AutoPostBack for Dropdownlist box, you need to set it property to true.

    Here is a sample for it. http://www.codeproject.com/KB/aspnet/CitiesDropDownList.aspx


    Failure in Life is failure to try...
    Wednesday, March 17, 2010 3:06 PM
  • AkimZ,

    I am aware of the data bindings in the dropdownlist in asp.net and the autopostback, I'm trying to figure out how to write the select statement to be based off the value of the Unit Type in the Unit Data.  I've never had to try to get the value like this before that is the reason why i asked.
    Wednesday, March 17, 2010 3:29 PM
  • You can also try to create a view visually in SSMS, then it automatically will create JOINs for you based on PK/FKs
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 17, 2010 3:38 PM
  • Will do.
    Wednesday, March 17, 2010 3:43 PM
  • I think your suggestion might work using the sample code, however I'm doing all this in a DetailsView control in Insert and Edit.  I am unable to see other dropdowns unless they are in the same template.
    Wednesday, March 17, 2010 4:35 PM
  • Any progress?
    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Monday, March 22, 2010 4:15 PM
  • Akim was right this is more of a asp.net issue than sql
    Tuesday, March 23, 2010 11:46 PM
  • If your looking for the asp.net solution this should help.

    http://forums.asp.net/t/1537274.aspx?PageIndex=1

     

    I did figure this out by doing as Naom and AkimZ suggested. 

    Wednesday, January 12, 2011 1:23 PM