Dawid77 Dawid77 - 18 days ago 7
MySQL Question

How to get data from two tables from database?

Can you explain me, why this code not working?

SELECT SUM(`cash`) AS `cash`,COUNT(*) AS `rows` FROM `table_1` WHERE `login` = 'test' UNION ALL SELECT COUNT(*) AS `rows2` FROM `table_2` WHERE `login` = 'test';


In phpMyadmin I see this message:


1222 - The used SELECT statements have a different number of columns


And I could not solve this problem.

Answer

A UNION takes the results of multiple SELECT statements and presents them as a single result set. But in order to do this, the number of columns in the individual SELECT statements has to be the same.

To understand this, it may help to format the query a bit:

    SELECT
        SUM(`cash`) AS `cash`,
        COUNT(*) AS `rows`
    FROM `table_1`
    WHERE `login` = 'test'
UNION ALL
    SELECT
        COUNT(*) AS `rows2`
    FROM `table_2`
    WHERE `login` = 'test'

Your first query is selecting two columns, cash and rows. The second query only selects one column, rows2. Also note that since UNION is concatenating the results, you may as well call the corresponding columns in each query by the same name.

If you really don't have any values that you want to select from the second table, you can substitute a default value for the missing columns:

    SELECT
        SUM(`cash`) AS `cash`,
        COUNT(*) AS `rows`
    FROM `table_1`
    WHERE `login` = 'test'
UNION ALL
    SELECT
        NULL AS `cash`
        COUNT(*) AS `rows`
    FROM `table_2`
    WHERE `login` = 'test'