Selecting from multiple tables without conditions

This site is best viewed in a modern browser with JavaScript enabled.

Selecting from multiple tables without conditions

lilfellabob

Hey guys,

I'm trying to select and print data from two different tables.
The reason the data is in different tables is for use in other parts of my site (makes life way easier). But on one particular page I just want to get the data from both tables and print it all out.
Once I can do that I want to order it by a date column (so it doesn't matter which table it's from the newer one will come first).

I have tried something like:

"SELECT id.table1, id.table2 FROM table1, table2"

but it seems to print out repetitions randomly :S

I am calling it all through php, I normally use something like

while($row = mysql_fetch_array($sql_query))
{
    echo "the data from the row 'id' is: " . $row['id'];
}

Any way I can do this? The simpler the better as I really like to understand what I'm doing

Thanks guys!,

lilfellabob

WolfShade

"SELECT id.table1, id.table2 FROM table1, table2"

This is looking for columns 'table1' and 'table2' in an aliased table (id) that hasn't been defined.

"SELECT table1.id, table2.id FROM table1, table2"

lilfellabob

thanks for the reply.
It fixed the error but it still loops through some row's many many times (no idea why..)

Ultimater

Try:

$sql="SELECT table1.id AS `id`,'table1' AS `table` FROM table1 UNION ALL SELECT table2.id,'table2' AS `table` FROM table2";
$query=mysql_query($sql) or die(mysql_error());
$results=array();
while($row=mysql_fetch_array($query)){
	$results[]=array('id'=>$row['id'],'table'=>$row['table']);
}
echo '<pre>'.htmlentities(print_r($results,true)).'</pre>';

You might also be interested in something complex to simulate a horizontal or column concentration:

SET @cnt1 := 0,@cnt2 := 0,@cnt3 := 0,@cnt4 := 0;
SELECT t1.id AS `id1`,t2.id AS `id2`FROM (SELECT *,++@cnt1 := @cnt1 + 1 AS `` FROM table1) AS t1 LEFT JOIN (SELECT *,@cnt2 := @cnt2 + 1 AS `` FROM table2) AS t2 ON t1.``=t2.`` UNION
SELECT t1.id AS `id1`,t2.id AS `id2`FROM (SELECT *,++@cnt3 := @cnt3 + 1 AS `` FROM table1) AS t1 RIGHT JOIN (SELECT *,@cnt4 := @cnt4 + 1 AS `` FROM table2) AS t2 ON t1.``=t2.``

But it would make more sense to just preform a second query in most cases.

Depending on the data in your tables, if the two tables have a field in common such as an id, you would probably be better off doing:

$sql="SELECT * FROM `table1` t1 LEFT JOIN `table2` t2 ON t1.id=t2.id";

Or if the two tables have different amount of rows, like in most cases, you can be safe using:

$sql="SELECT * FROM `table1` t1 LEFT JOIN `table2` t2 ON t1.id=t2.id UNION SELECT * FROM `table1` t1 RIGHT JOIN `table2` t2 ON t1.id=t2.id";

But that is usually unnecessary if the first table is the more important one. If not, simply switch the table1 and table2 references or swap the LEFT JOIN for a RIGHT JOIN.

lilfellabob

Hmm yeah I thinking that first lot is what I'd use. But I decided that I'd be better off putting it in one table then adding a field to split it up later.

Thanks for the help guys, really appreciate it!

lilfellabob