CODEWITHSUNDEEP
mysqlsqljoinleft-join
Fer
Fer

Reputation: 1

Mysql join making all join

I am trying to access the database to two tables. In a store I keep a list of events that has a

Table Event
id, name,datei,houri, dateF,Hourf ,capacity, age ,description,image,url, local

Table Assistance
visibility, event, client

To access the list of events given an id I have

SELECT * from event where local = '1'

To access the number of people attending an event I have

SELECT count (*) as assistants FROM Assistance WHERE event = '1'

But to put together the two things I have

SELECT e. *, COUNT (a.client) AS asis FROM event e LEFT JOIN assistance to ON e.eid = a.event where e.locales = '1' GROUP BY a.evento

Here in the latter he takes me the events he wants and sometimes only when he has assistance.

Use mysql and all join is not available.

I tried debugging trying different options but I have not been successful. If it is necessary to make a contribution or clarification, indicate me. The numbers 1 are an example.

Upvotes: 0

Views: 42

Answers (2)

spencer7593
spencer7593

Reputation: 108530

To get a count from a related table, we can do a correlated subquery in the select list

SELECT e.*
     , ( SELECT COUNT(1)
           FROM assistance a
          WHERE a.event = e.eid
       ) AS asis
  FROM event e
 WHERE e.locales = '1'

or we can do aggregation in an inline view, and then join to that

SELECT e.*
     , IFNULL(c.cnt,0) AS asis
  FROM event e
  LEFT
  JOIN ( SELECT a.event
              , COUNT(1) AS cnt
           FROM assistance a
          GROUP
             BY a.event
       ) c
    ON c.event = e.eid
 WHERE e.locales = '1'

The specification (i.e. what the query is supposed to return) isn't clear from the question, with only the broken SQL to go by, we're just guessing at the specification.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You are aggregating by a column in the second table of a LEFT JOIN. This might be NULL. You want to aggregate by the first table; and if you use a single column, it should be the primary key or unique.

So:

SELECT e.*, COUNT(a.event) AS asis  -- if you want to count matches, use a key in the ON clause
FROM event e LEFT JOIN
     assistance a
     ON e.eid = a.event 
WHERE e.locales = 1  -- looks like a number so I assume it is and removed the single quotes
GROUP BY e.eid;

Upvotes: 1

Related Questions