SQL statement

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Zeff
    #1

    SQL statement

    Hi all,

    I hope someone can help me with the following,
    I have several (lookup) tables, were id's and names are stored.
    All table all brought together by relationships (many to 1) in the
    'master' table (by referring to the id's of the records in the lookup
    tables).
    I want to make a query where all names are looked up using joins:

    SELECT
    s.[id_master],
    s.[date],
    s.[description],
    s.[table_x_id],
    s.[table_y_id],


    x.[table_x_name],
    y.[table_y_name]

    FROM
    tblMaster s
    join tblX x on s.table_x_id = x.table_x_name
    join tblY y on s.table_y_id = y.table_y_name
    ;

    Showing me the corresponding names from tables tblX and tblY, instead of
    the id's (that are stored in tblMaster)...

    Looks simple, but the SQL statement from above doesn't work!

    Many thanks in advance!

    Zeff
    Tags: None
    • Bruce
      #2
      Re: SQL statement

      On Feb 13, 10:56 am, Zeff <z...@trash.net wrote:
      Hi all,
      >
      I hope someone can help me with the following,
      I have several (lookup) tables, were id's and names are stored.
      All table all brought together by relationships (many to 1) in the
      'master' table (by referring to the id's of the records in the lookup
      tables).
      I want to make a query where all names are looked up using joins:
      >
      SELECT
      s.[id_master],
      s.[date],
      s.[description],
      s.[table_x_id],
      s.[table_y_id],
      >
      x.[table_x_name],
      y.[table_y_name]
      >
      FROM
      tblMaster s
      join tblX x on s.table_x_id = x.table_x_name
      join tblY y on s.table_y_id = y.table_y_name
      ;
      >
      Showing me the corresponding names from tables tblX and tblY, instead of
      the id's (that are stored in tblMaster)...
      >
      Looks simple, but the SQL statement from above doesn't work!
      >
      Many thanks in advance!
      >
      Zeff
      I'm just guessing...shou ld your joins be

      join tblX x on s.table_x_id = x.table_x_id
      join tblY y on s.table_y_id = y.table_y_id

      instead of

      join tblX x on s.table_x_id = x.table_x_name
      join tblY y on s.table_y_id = y.table_y_name

      ?

      Bruce

        Comment

        • Zeff
          #3
          Re: SQL statement

          Dear Bruce,

          Thanks for your quick reply. For posting, I made a kind of 'abstraction'
          of the original query. Actually, my complete query looks like this:

          SELECT
          s.[id_sample],
          s.[reference],
          s.[date],
          s.[ref_to_id_files],
          s.[ref_to_id_speci es],
          s.[ref_to_id_ident ification],

          f.[name files],
          i.[name identification],
          s.[name species]

          FROM
          tblSample s
          JOIN tblFiles f on s.ref_to_id_fil es = f.id_files
          JOIN tblIdentificati on i ON s.ref_to_id_ide ntification = i.id_identifica tion
          JOIN tblSpecies s ON s.ref_to_id_spe cies = s.id_species;

          Hope someone can help...

          Many thanks!

          Zeff.

          Bruce wrote:
          On Feb 13, 10:56 am, Zeff <z...@trash.net wrote:
          >Hi all,
          >>
          >I hope someone can help me with the following,
          >I have several (lookup) tables, were id's and names are stored.
          >All table all brought together by relationships (many to 1) in the
          >'master' table (by referring to the id's of the records in the lookup
          >tables).
          >I want to make a query where all names are looked up using joins:
          >>
          >SELECT
          >s.[id_master],
          >s.[date],
          >s.[description],
          >s.[table_x_id],
          >s.[table_y_id],
          >>
          >x.[table_x_name],
          >y.[table_y_name]
          >>
          >FROM
          >tblMaster s
          >join tblX x on s.table_x_id = x.table_x_name
          >join tblY y on s.table_y_id = y.table_y_name
          >;
          >>
          >Showing me the corresponding names from tables tblX and tblY, instead of
          >the id's (that are stored in tblMaster)...
          >>
          >Looks simple, but the SQL statement from above doesn't work!
          >>
          >Many thanks in advance!
          >>
          >Zeff
          >
          I'm just guessing...shou ld your joins be
          >
          join tblX x on s.table_x_id = x.table_x_id
          join tblY y on s.table_y_id = y.table_y_id
          >
          instead of
          >
          join tblX x on s.table_x_id = x.table_x_name
          join tblY y on s.table_y_id = y.table_y_name
          >
          ?
          >
          Bruce
          >

            Comment

            • Jens Schilling
              #4
              Re: SQL statement

              Hi,
              I hope someone can help me with the following,
              I have several (lookup) tables, were id's and names are stored.
              .......
              .......
              Showing me the corresponding names from tables tblX and tblY, instead
              of the id's (that are stored in tblMaster)...
              That's the behavior and evil of Lookup Fields ......

              You can read more about it here :

              The Access Web - The Evils of Lookup Fields in Tables
              http://www.mvps.org/access/lookupfields.htm


              Regards
              Jens


                Comment

                Previous template Next
                Working...