SELECT Multiple Tables

Question

I'm stuck in a piece of code that does not quite understand. I have several tables with different names but same fields, but the tables are independent Something like that:

table1

id
user
title

table2

id
user
title

I need to get in the same query data from two tables but I fail, I try with INNER JOIN, UNION ALL, but not knowing, it misapplied. Right now I have this:

$mysites = $db->QueryFetchArrayAll("
select * 
FROM table1,table2
where table1.user = table2.user AND 
table1.user = 1");

foreach($mysites as $mysite){
echo $QUERY['title'];
}

but returned this:

title1.table1

title2.table1

and i like this:

title1.table1

title2.table1

title1.table2

title2.table2

A greeting and thanks

What is `$mysites` and `$QUERY`? Are they defined? Note that case matters with PHP variables. What class are you using? — halfer, Apr 30 '15 at 10:52
Hi, thanks for comment, I tried this (select table1.*, table2.*) but it gives the same result. I've corrected the variable $mysites Any more ideas? — Esweb, Apr 30 '15 at 10:56
Look at this answer: http://stackoverflow.com/a/17692215/2568469 — hatef, Apr 30 '15 at 11:05

score 1 · Accepted Answer · answered Apr 30 '15 at 11:02

You can use the keyword UNION like this:

  SELECT * FROM table1 UNION SELECT * FROM table2

This query will select everything from table1 and merge the results with those from table2. Please note that you have to select the same number of columns from both tables. Moreover, column names and datatypes will be assigned according to first table.

If you want to preserve duplicates add the keyword ALL:

  SELECT * FROM table1 UNION ALL SELECT * FROM table2

score 0 · Answer 2 · answered Apr 30 '15 at 10:59

0

The question is very unclear.....

Are the ID's the same in each table for each user? If so an INNERJOIN will help

SELECT t1.*, t2.* 

FROM table1.t1 

INNER JOIN table2.t2 

ON t1.id = t2.id 

WHERE t1.user = "1"

(Change INNER JOIN to LEFT JOIN if the data could be missing)

If this is not the case, why not put the data from one table into the other, and have just one table with all the data in it?

answered Apr 30 '15 at 10:59

This not return any value ... Each table handles a lot of data and need to have them separated in 2 different tables. Thanks for answering – Esweb Apr 30 '15 at 11:04

SELECT Multiple Tables

2 Answers2