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

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…?

Dammit, wrong account!

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

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.

I think a query like this might work


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’.

Thanks. This works great. Much apprciated!