Problems with JOIN MySql

Question

Problems with JOIN MySql

Navigation

#1 by (1 votes)

1

Dear friends, I really have a serious problem, I have several tables that store certain information of users such as their facebook, twitter, instagram, image, email, etc ...

I will show the SQL and explain the problem:

SELECT  
        IFNULL(empresa_twitter.twitter,users.twitter) AS    user_twitter,
        users.id        AS  user_id,
        users.status        AS  user_status,
        users.grants        AS  user_grants,
        Lower(users.admin)  AS  user_admin,
        users.webmail       AS  user_webmail,
        users.PLAN      AS  user_plan,
        users.username      AS  user_username,
        users.facebook      AS  user_facebook,
        users.instagram     AS  user_instagram,
        users.twitter       AS  user_twitter,
        Trim(Lower(users.nombre))AS user_nombre,
        Trim(users.about)   AS  user_about,
        Lower(users.email)  AS  user_email,
        IFNULL(Lower(empresa_imagen.imagen),users.imagen)   AS  user_imagen,
        IFNULL(Lower(empresa_facebook.facebook),users.facebook) AS  user_facebook,
        IFNULL(Lower(empresa_instagram.instagram),users.instagram)  AS  user_instagram,
        Lower(paises.pais)  AS  pais_nombre,
        Lower(paises.iso2)  AS  pais_code,
        Lower(paises.moneda)    AS  pais_moneda,
        Lower(paises.simbolo_moneda)    AS  pais_simbolo,
        Lower(paises.bandera)   AS  pais_bandera,
        IFNULL(SUM(ingresos.monto), 0)  AS  ingresos,
        IFNULL(SUM(egresos.monto),  0)  AS  egresos
FROM users, paises
LEFT    JOIN ingresos ON    ingresos.user   =   1907
LEFT    JOIN egresos ON egresos.user    =   1907
LEFT    JOIN empresa_imagen ON  empresa_imagen.user =   1907
LEFT    JOIN empresa_facebook ON    empresa_facebook.user = 1907
LEFT    JOIN empresa_instagram ON   empresa_instagram.user = 1907
LEFT    JOIN empresa_twitter ON empresa_twitter.user = 1907
WHERE users.id  =   1907
  AND   paises.iso2 =   users.pais

The problem exists when in the first SELECT I look for the twitter in the table empresa_twitter, since it shows me the twitter stored in the users table in case company_twitter is NULL or empty, but:

It does not show me the most recent record (the last record), I try to make a MAX (IFNULL ()) but it causes me an error, I just want to get the last record of your twitter, facebook, instagram, image, etc ... But at the same time add it to the results ...

1, user, email, facebook.com, twitter.com, instagram.com, etc ...

Company_facebook table

_____________________________________________________________
| ID  |      user      | user_facebook |        fecha        |
--------------------------------------------------------------
|  56 |    1907        | axer.xiche    | 0000-00-00 00:00:00 |
--------------------------------------------------------------
|  57 |    1907        | arc.guevara    | 0000-00-00 00:00:00 |
--------------------------------------------------------------

Table users

_________________________________________________________________
|   ID  |      username  | user_facebook   |       fecha         |
------------------------------------------------------------------
|  1907 |    arcaelas    | usuario_default | 0000-00-00 00:00:00 |
------------------------------------------------------------------

Now what I need is for him to select the fields in the users table where id = 1907 and the last record in the twitter_table table, but in case there is no record in the table empresa_twitter show me the result of the users table, I did it with a


    IFNULL(empresa_twitter.twitter, users.twitter)

And there we go well, but everything gets damaged when I want to show me the last record since I tried it with


    MAX(IFNULL(empresa_twitter.twitter, users.twitter))
    LAST(IFNULL(empresa_twitter.twitter, users.twitter))

And they return error, what should I do or how should I execute the query?

What I try is not to perform separate queries to avoid more weight on the server, since MySql knows how it works, unlike Postgrel, Whoever has a good solution would appreciate it in the soul and the company too.

mysql sql php join

asked by Arcaela 25.03.2018 в 20:27

source

1 answer

Perform a query using a select in HTML Prevent browser from using cache with ajax

score 1 · Accepted Answer

I can not understand 100% what you want to do.

First you must change your code in the left join in this way:

LEFT    JOIN ingresos ON ingresos.user   =   users.id
LEFT    JOIN egresos ON egresos.user    =   users.id
LEFT    JOIN empresa_imagen ON  empresa_imagen.user =   users.id
LEFT    JOIN empresa_facebook ON empresa_facebook.user = users.id
LEFT    JOIN empresa_instagram ON empresa_instagram.user = users.id

I recommend using order by to order the result for each company table (company_facebook, company_twitter) as follows:

WHERE users.id  =   1907
 AND  paises.iso2 =   users.pais
ORDER BY empresa_twitter.id DESC, 
         empresa_facebook.id DESC, 
         empresa_imagen.id DESC,
         empresa_instagram.id DESC;

With order b and you would be ordering the result of the query, and the number of records will vary according to your stored data.

If you want to return a single record for each user you can add group by or even limit 1

Another alternative to devlver a single record would be subqueries instead of the left joins in this way:

IFNULL(Lower(select imagen from empresa_imagen where empresa_imagen.user = users.id order by id desc limit 1),users.imagen) AS  user_imagen,