Display a mysql table with JOIN and NULL values

Navigation
2

I have a table in mysql base, where are the records of all the teams (it is for a soccer page) I need to pair it with two tables but only counting some values. For example:

Base table called "computers", where the other data will be taken 

id      equipo
(int)   (varchar)  
-------------------
afc     Aragua
ccs     Caracas
dan     Anzoátegui

This table is what I want to match called "data". 

id      equipo1      equipo2       data3
(int)   (varchar)   (varchar)       (int)
-------------------------------------
1       dan           ccs         456
2       ccs           dan         654
3       ccs           dan         666

Then I have dealt with: 

SELECT eq.id, eq.equipo, count(dat1) AS dato1, count(dat2) AS dato2 
FROM equipos AS eq 
INNER JOIN datos AS dat1 ON dat1.equipo1 = eq.id 
INNER JOIN datos AS dat2 ON dat2.equipo2 = eq.id 
GROUP BY eq.id 
ORDER BY eq.equipo ASC

I know it does not work like that, but I would like to see something like this: 

equipo      dato1      dato2
-------------------------------
dan           1          2
ccs           2          1
afc           0          0

The problem I have is that the afc value does not appear is NULL in the database "data" then it does not appear in the search.

    
asked by Armando García 07.12.2016 в 03:53
source

2 answers

2

You can use sub-queries, like this: 

select   eq.id
       , (select count(1) from datos AS d where d.equipo1 = eq.id) as dato1
       , (select count(1) from datos AS d where d.equipo2 = eq.id) as dato2
  from equipos as eq
    
answered by 07.12.2016 / 04:07
source
1

For the same reason that certain computers in the datos table may be missing, you need to use a LEFT JOIN .

Here is a way to ask: 

select eq.id, 
       eq.equipo,
       coalesce(d1.cnt, 0) as dato1,
       coalesce(d2.cnt, 0) as dato2
  from equipos eq
  left join (select equipo1, count(*) as cnt
               from datos
              group by equipo1) d1
    on d1.equipo1 = eq.id
  left join (select equipo2, count(*) as cnt
               from datos
              group by equipo2) d2
    on d2.equipo2 = eq.id
 order by eq.equipo

Demo .

Edit

You will notice that @jachguate gave you a good answer also for your situation. And if you have a little data, it does not matter what query you use. But if you have a larger amount of data, I encourage you to compare the performance. Normally, using the joins should result in better performance than using sub queries, but you will have to take the test to verify.

    
answered by 07.12.2016 в 04:06
AngularJS request header field Content-Type is not allowed by Access-Control-Allow-Headers Sort Array of a file_get_html