Reputation: 1
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
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
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