[RESOLVED] Getting Data from 3 tables ?

Mark_Nordstrom

Ok heres my code

SELECT r.name, COUNT(a.event)/COUNT(e.event) AS total FROM Attendance AS a, Ratio AS r, Event AS e WHERE r.name=a.name GROUP BY r.name

I do this in mysql and the process hangs forever like an infinite loop. I dont know what im doing wrong. I event try adding LIMIT 5 and it still hangs so something is wrong.

Ok ive worked at it and if I take the Event table out of this quey it works that is what seems to make it hang, when I combine Event and Attendance.

SELECT r.name, COUNT(a.event) AS total FROM Attendance AS a, Ratio AS r WHERE r.name=a.name GROUP BY r.name

This works but not what im trying to get.

Bunkermaster

why would you divide COUNT(a.event)/COUNT(a.event)? you'll always get 1
I suggest you look into join and union too (SQL statements).

Piranha

The problem is that you don't have any realtionship between event and any of the other tables. The result is that every row in event will make a relationship with every other row in the database. It is easier to explain with two tables.

Let's say that you have table attendance and ratio, attendance have 100 rows and attendance 200 rows. If you do a query "SELECT something FROM ratio, attendance" you will get 200*100 rows, that is 20.000 rows in return. But if you instead do a join "SELECT something FROM ratio, attendance WHERE ratio.name = attendance.name" you will get about 200 rows, maybe a little less.

Mark_Nordstrom

Piranha wrote:
The problem is that you don't have any realtionship between event and any of the other tables. The result is that every row in event will make a relationship with every other row in the database. It is easier to explain with two tables.

Let's say that you have table attendance and ratio, attendance have 100 rows and attendance 200 rows. If you do a query "SELECT something FROM ratio, attendance" you will get 200*100 rows, that is 20.000 rows in return. But if you instead do a join "SELECT something FROM ratio, attendance WHERE ratio.name = attendance.name" you will get about 200 rows, maybe a little less.

SELECT r.name, COUNT(a.event)/COUNT(e.event) AS total FROM Attendance AS a, Ratio AS r, Event AS e WHERE r.name=a.name GROUP BY r.name

Ok so how would I join this ? Sorry if I sound confused. Been working at this for days now cant get it to work.