CODEWITHSUNDEEP
phpmysql
easy pz
easy pz

Reputation: 69

php/mysql Merge 2 tables with same Columns correctly

EDIT: Thanks For JoseCarlosPB here is the solution:

SELECT
  (select count(DISTINCT userscount) AS duplicates from users1)
  +
  (select count(DISTINCT userscount) AS duplicates from users2) AS duplicates
FROM dual;

So i have 2 tables and a mysql statement i want to merge 2 tables but there is same column in both.I think There is nothing to do in PHP code i think the problem is in the mysql statement.

my code works(no errors) but it gives unexpected output.i simply want to correctly count userscount here is what i mean: table1 has 32 users and table2 has 44 users i thought the output will be 76 users but it is 3244

PHP:

include 'conn00.php';
    $sql = "select DISTINCT userscount, count(DISTINCT userscount) AS duplicates from users1 UNION ALL
     select DISTINCT userscount, count(DISTINCT userscount) AS duplicates from users2";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $row["duplicates"];
        }
    } else {

    }

table1:

users1 

32

table2:

users2 

44

output: 3244 i want the output to be 76

If the solution is in mysql will be better bcz i have more similar sql statements and i want apply the solution for all of it. thanks for answering

Upvotes: 4

Views: 1393

Answers (6)

HariKishore
HariKishore

Reputation: 169

SELECT * from ( (select count(DISTINCT userscount) AS duplicates from users1) union all (select count(DISTINCT userscount) AS duplicates from users2) ) as duplicates

You can also merge in this way.hope it was helpful

Upvotes: 0

JoseCarlosPB
JoseCarlosPB

Reputation: 875

I think you can do it this way, almost like alistaircol said

SELECT
  (select count(DISTINCT userscount) AS duplicates from users1)
  +
  (select count(DISTINCT userscount) AS duplicates from users2) AS duplicates
FROM dual;

Upvotes: 7

user12313683
user12313683

Reputation:

I am not sure, if the semantics of the query was correct, but the syntax must be like this (WHERE clause goes after JOIN and ON): 

 SELECT users1.text1, users2.name,users2.email, 
 users1.username
 FROM users1 
 INNER JOIN users2 
 ON users1.text1=users2.text1
 WHERE users1.username = $_SESSION[username]

Upvotes: 0

dennisgon
dennisgon

Reputation: 347

maybe you can change your code query into this to sum the entire counting value 

      include 'conn00.php';
    $sql = "select  sum(i) as duplicates from (
    select count(DISTINCT  userscount) as i from users1 union all
    select count(DISTINCT  userscount) as i from users2 
    ) x";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $row["duplicates"];
        }
    } else {

    }

Upvotes: 0

alistaircol
alistaircol

Reputation: 1453

Maybe you could try something like this for your query:

SELECT
  (select count(DISTINCT userscount) AS duplicates from users1 group by usercount)
  +
  (select count(DISTINCT userscount) AS duplicates from users2 group by usercount)
FROM dual;

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

More info about DUAL: https://dev.mysql.com/doc/refman/8.0/en/select.html

Upvotes: 2

Sinisa Bobic
Sinisa Bobic

Reputation: 1311

Please check article you need Combine two MYSQL table with same column Name

Just comparing to this you need to move count outside of UNION in order to work as you expected

Upvotes: 3

Related Questions