Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16

    Unanswered: find matching Product ID in two different tables

    I have two different Databases one for Production one for the Lab.
    In my LabDB i have a Table where every order is recorded with CustomerInfo and so on and so forth. At the end we assign a Product ID.

    In the Production DB i have a table called invoices where all Productions ever sent are recorded There it also lists the Product ID how can i make a Table that looks for matching Product Ids in the two tables.
    I am trying to find out if the LabOrdered created an Order.

  2. #2
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16

    PLEASE HELP

    PLEASE HELP A NOVICE

  3. #3
    Join Date
    Jan 2004
    Posts
    492

    Re: PLEASE HELP

    Do you have 2 physically different databases (2 separate files?)

    or

    Do you have just 2 different tables in that same database?


    I assume its the latter -

    If you want product ID's that are only in both tables do something like this:

    Code:
    SELECT lab.col1, lab.col2, lab.product_id
    from lab, production p
    where lab.product_id = p.product_id
    Where lab.col1 and lab.col2 are any columns from the LAB table that you want - just replace col1 and 2 with the names... possibly like lab.customer_name, lab.address..etc

    If this isnt what you want you must be more clear on what you are trying to get.

  4. #4
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16
    It`s 2 seperate files but i know how to import the table so that`s not the problem.
    Let me see if i understand i make a new table and say i call it Products Ordered then i make 4 fields all text where do i put in that code????

    p.s. thank you so much for your help

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by MANIMORE
    It`s 2 seperate files but i know how to import the table so that`s not the problem.
    Let me see if i understand i make a new table and say i call it Products Ordered then i make 4 fields all text where do i put in that code????

    p.s. thank you so much for your help
    Import the file - What you really should have is 3 tables - one listing customer info, one listing product info, and an intersection table listing transactions. The 3rd table would have foreign keys from product and customer. But if you dont want to rework your database, here's how to do it with 2 tables.

    Now in the same database you have two tables Invoices and Customer


    Customer has basic info:

    Customer_id
    customer_name
    address
    phone
    product_id

    Invoices has your product transactions:

    product_id
    description


    So to find the customer name, address and product description of products in BOTH tables:

    Design a new query - switch to SQL View from the view menu ( i think thats where its located) and paste this in: Note you may have to switch the column names slightly to accomodate for your DB.

    Code:
    select c.customer_name, c.address, i.description
    from customer c, invoices i
    where c.product_id = i.product_id
    Basically I created an alias on both tables ( c and i ) this just allows me to shorthand the table names in the SELECT statement. You put in the columns from the respective tables that you want to see. The join statement in the WHERE clause tells the DB to pull back only product id's that are in both tables.

    Hope that helps!

  6. #6
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16
    i am gonna try this thanks so much.

  7. #7
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16
    WORKS LIKE A CHARM THANK YOU. NOW MY PROBLEM IS IN THE PRODUCT CODE OF THE INVOICE TABLE WHEN A PRODUCT CODE IS 349/91260 IT IS STORED THAT WAY IN MY LAB PROJECTS TABLE THE CODE IS STORED WITHOUT THE / BUT I`LL FIGURE THAT OUT.
    THANKS

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by MANIMORE
    WORKS LIKE A CHARM THANK YOU. NOW MY PROBLEM IS IN THE PRODUCT CODE OF THE INVOICE TABLE WHEN A PRODUCT CODE IS 349/91260 IT IS STORED THAT WAY IN MY LAB PROJECTS TABLE THE CODE IS STORED WITHOUT THE / BUT I`LL FIGURE THAT OUT.
    THANKS
    You really should look into making sure the values are stored the same way.

    As a workaround you could try this (untested but should work):

    Code:
    select c.customer_name, c.address, i.description
    from customer c, invoices i
    where c.product_id = i.product_id
    or
    (substr(i.product_id, 1, instr('/')-1) = c.product_id
    and substr(i.product_id, instr('/')+1)=c.product_id)
    I havent tested this out, but I recommend that you change your DB design, and/or make sure the product Id's match.

Posting Permissions

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