select multiple tables mysql

Question

I am trying to select two tables with where clause,

The problem: I am getting more than 2 result. something like 123451111 I only have two ids with the value 1. I think I am doing it wrong.

The tables don't have the same structure and are not related by any means. Any ideas?

<?php include_once("config.php");
   $s = '1';
$stmt =$mydb->prepare("select * FROM table1,table2 where table1.id = ? or table2.id = ?");
stmt->bind_param('ss', $s, $s);
echo $mydb->error;
$stmt->execute();

?>
<?php
$results  = $stmt->get_result();
while ($row = $results->fetch_assoc()) {
echo $row['id']."<br/>";
}
?>

Solution 1

You need to have a join between table1 and table2 on some unique column, say id.

select * FROM table1,table2 where table1.id = table2.id;

Additionally you can have multiple filter conditions( say you want to filter the tables on id=101 -

select * 
FROM table1,table2 
where table1.id = table2.id
and table1.id = 101;

Hope this helps. Whenever you have multiple tables in a SQL statement, you need to join them otherwise the engine would make cartesian product as it happens in Cartesian product of mathematical set theory.

Basically you should have at least n-1 join conditions where n is the number of tables used.

Solution 2

Your question is a little problematic, but if your problem is not getting two id's, but you are getting one correctly with the use of a JOIN, you may be looking for a IN clause:

SELECT * 
FROM table1,table2 
WHERE table1.id = table2.id
AND table1.id IN (ID1, ID2);

Using IN instead of = lets you match multiple values to the table.id. This way, you get data from both tables and you get both ID's

Solution 3

This is join usage :

select t1.*,t2.* FROM table1 t1
left join table2 t2
on t1.id = t2.id
where t1.id = "keyword"