How to query two tables and merge results into one table

[DirtyDog]

I’m trying to move a site away from a CMS and I need to get the data from two existing tables and merge it into one table. I have queried the first table for a list of record ID’s and titles. I now need to use these record ID’s to query the related info from the second table. I need to merge the results of this second query with the results from the first to make one table. This is a MySQL database and I have Phpmyadmin, and PHP to use as tools for this if needed.

Any suggestions on how to go about this? I basically need a SELECT from table2 WHERE recordID = x and loop it through the values for x, then get the results as a csv file.

[Amit_Yaron]

If both tables have the same number of columns, you can try:

select * from table1
union
select * from table2;

[SpacePhoenix]

What fields do you have in each table? What’s the required output?

[DirtyDog]

Basically the problem looks like this:

In table 1 I need to get a certain set of record ID’s:

SELECT table1.recordID WHERE table1.contentType = 2

In table 2 I need to query all the records that match those ID’s. So

SELECT table2.title WHERE table2.recordID = table1.recordID

So that second query needs to run for every record ID value returned in the first query. I’m guessing this can’t be done in SQL so I’m thinking a script needs to be made in PHP to do this?

[Amit_Yaron]

You can do it in one query:

SELECT table1.recordID , table2.title
FROM table1
INNER JOIN table2
ON table2.recordID = table1.recordID
WHERE table1.contentType=2

[DirtyDog]

Awesome thanks!