SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face newbie here - need to replace data across 3 tables

    I am not a programmer, well I know action script so here's my best attempt to explain what I need.

    I have 3 tables with different data.
    Table 1 is 'products' and has 'id' and 'name' columns
    Table 2 is 'safety_documents' and has 'id' and 'title' columns
    Table 3 is 'product_documents' and has 'product_id' and 'document_id' columns

    I need to find and replace Table 1 'id' with Table 2 'title'

    There is more than 1 product_id with the same value, meaning 1 product_id can have 0 up to 7 document_ids

    Ideally I need to create a new table that has:
    product_id, product_name, document_title 1, document title 2,...document title X

    Any help is appreciated
    Last edited by at836; Apr 12, 2010 at 18:25. Reason: omit name

  2. #2
    SitePoint Member test's Avatar
    Join Date
    Dec 2007
    Location
    ddf
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like you don't need a new table, can you give an example of what the data is going to be used for?

    Do you want a result set like
    PID1 DOC1
    PID1 DOC2
    PID1 DOC3
    PID2 DOC4
    PID2 DOC5

    etc..?
    test1

  3. #3
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Dammit, wrong account!

  4. #4
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The output result can be in excel or as CSV.
    I'm trying to generate a simple report but there are 1000s of entries for copy/paste

    yes this format will work:
    PID1 DOC1
    PID1 DOC2
    PID1 DOC3
    PID2 DOC4
    PID2 DOC5

    where
    PID is a product name;
    DOC is document title

  5. #5
    Galactic Overlord gold trophysilver trophybronze trophy
    HAWK's Avatar
    Join Date
    Aug 2003
    Location
    New Zealand
    Posts
    12,540
    Mentioned
    956 Post(s)
    Tagged
    14 Thread(s)
    Off Topic:

    For any of the number of people that have reported Mal's post above... there is nothing rude or suspicious about it! He accidentally posted from one of our test accounts and then as himself.

  6. #6
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    I think a query like this might work
    Code SQL:
    SELECT
    p.id, d.id
    FROM 
    products p, safety_documents s_d, product_documents d
    WHERE 
    p.id = s_d.product_id
    AND 
    s_d.document_id = d.id

    This will do an inner join that will return a row for every row in the safety_documents table, and link the appropriate row in the products and documents table.

    You would add all the columns you want in the SELECT part, accessing the columns in the products table with the 'p' alias, e.g. p.name etc. and the documents table with 'd'.

  7. #7
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. This works great. Much apprciated!


Tags for this Thread

Bookmarks

Posting Permissions

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