PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Select statement syntax for a linked JET table-VBForums
Results 1 to 2 of 2

Thread: Select statement syntax for a linked JET table

  1. #1

    Thread Starter
    Junior Member Dawg's Avatar
    Join Date
    Nov 2009
    Location
    Portland Or, USA
    Posts
    26

    Select statement syntax for a linked JET table

    First let me apologize for this not being directly a VB question, however I hope to find an answer and that the answer may help others.

    I have a .JET database. I have a Program table with a P_ID and a P_NAME. I have a Chemical table with a C_ID and a C_NAME.

    I associate records between the two tables with a third table LinkProgramChemical, where I have a P_ID and a C_ID column that are related to their respective primary keys in their respective tables.

    I want to query the link table from VB using a Select statement, but I don't want to deal with knowing the ID's of the Programs and/or Chemicals - I want to do it with the Name's, and have some other mechanism deal with associating the numbers to the names.

    Oracle allows this with Views in the database. How does one do it with a JET database? Is it a manual endeavor - meaning I have to do the leg work to get the ID's and subsequently query their associated names, and visa versa?

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,387

    Re: Select statement syntax for a linked JET table

    Something like this:

    sql Code:
    1. SELECT
    2.     P_Name AS Program_Name,
    3.     C_Name AS Chemical_Name
    4. FROM
    5.     Program
    6. INNER JOIN LinkProgramChemical
    7.     ON Program.P_ID = LinkProgramChemical.P_ID
    8. INNER JOIN Chemical
    9.     ON LinkProgramChemical.C_ID = Chemical_C_ID
    10. WHERE Program.P_Name = 'Some Program Name here'
    or

    sql Code:
    1. SELECT
    2.     P_Name AS Program_Name,
    3.     C_Name AS Chemical_Name
    4. FROM
    5.     Program
    6. INNER JOIN LinkProgramChemical
    7.     ON Program.P_ID = LinkProgramChemical.P_ID
    8. INNER JOIN Chemical
    9.     ON LinkProgramChemical.C_ID = Chemical_C_ID
    10. WHERE Chemical.C_Name = 'Some Chemical Name here'

    You will need to look at adding parentices around the joins (only if using MS Access)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width