CODEWITHSUNDEEP
phpmysqlselectjoin
Ali Hamra
Ali Hamra

Reputation: 232

SELECT data from two tables with same name

I've tried to find a way to select data from two tables within the same query as follows:

Suppose I have this as table1

id | item | qty
1  | 1bb1 | 12
2  | 1cc1 | 10

and as table2

id | item | qty
6  | 1bb1 | 12
7  | 1vv1 | 4

And I have an imported file which contains data item as $sheetData[$i]['A'] from an excel sheet that I need to use it to find out if BOTH tables have this item or not.

My code as follows :

$query1="SELECT * FROM table1.item,table2.item WHERE item ='".$sheetData[$i]['A']."'";

$result1= mysql_query($query1);
if(mysql_num_rows($result1)>0){

echo "This Item Found in Both Tables";
echo $sheetData[$i]['A'];
echo "<br />";
}
else{
    echo "Item Could Not Be Found in both tables";
    echo $sheetData[$i]['A'];
}

Its basically I want to find out if the imported item found in both tables or not. I hope this makes sense for you!

Any help would be really appreciated

Upvotes: 0

Views: 95

Answers (3)

Divyesh Savaliya
Divyesh Savaliya

Reputation: 2740

Compiler can't decide which item needs to compare. item from table1 or item from table2

So write query as:

SELECT A.* , B.* 
FROM table1 A, table2 B 
WHERE A.item  = B.item AND 
      A.item  = '".$sheetData[$i]['A']."'

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try something like this:

SELECT COUNT(*)
FROM (
  SELECT DISTINCT 1 
  FROM table1
  WHERE item = ?

  UNION ALL

  SELECT DISTINCT 1  
  FROM table2
  WHERE item = ?) AS t

This will return 2 if the item exists in both tables, 1 if it exists in only 1 table and 0 if the item doesn't exists in any of the two tables.

Upvotes: 0

Sougata Bose
Sougata Bose

Reputation: 31749

Try with - 

"SELECT t1.id, t1.item, t1.qty, t2.id id_2, t2.qty qty_2 FROM table1 t1
INNER JOIN table2 t2 ON t2.item = t1.item 
WHERE table1.item ='".$sheetData[$i]['A']."'"

Try to avoid using mysql, it is deprecated now. mysqli/PDO instead.

Upvotes: 0

Related Questions