GuitarGod

Thank you both sincerely! I've taken your advice Benanamen and changed the tables accordingly. And Barand, as ever, your code is spot on I've been trying to add a GROUP_CONCAT to the query so that if a student belongs to any schools, the school ID and name are returned. I'm not having much luck but I'll keep trying. Out of interest, is there an easy way to achieve this? Thanks once again for all your advice/help!

Hi all, I'm after some advice about these tables and whether I'm using them in the most efficient way. Currently I have 4 tables; Students, Schools, Administrators and School members. The Students table is simply a list of students with an 'admin_id' field - which relates to an administrator in the Administrators table who created the student (the student may not yet be a member of any School as explained further in the Schools and School members tables). STUDENTS +---------------+---------------+---------------+ | Student ID | Admin ID | Student name | +---------------+---------------+---------------+ | 1 | 1 | Mike | | 2 | 1 | Edward | | 3 | 1 | John | | 4 | 1 | George | +---------------|---------------+---------------+ The Administrators table contains the people who will use the script and oversee the Students/Schools table ADMINISTRATORS +---------------+---------------+ | Admin ID | Admin name | +---------------+---------------+ | 1 | Mr James | | 2 | Mr Davies | | 3 | Ms Lewis | | 4 | Mrs Walsh | +---------------|---------------+ Just from the example above, you can see that all current students have been registered by Mr James. The Schools table, similar to the Students table, is simply a list of Schools registered by an administrator (containing an 'admin_id' related field). SCHOOLS +---------------+---------------+---------------+ | School ID | Admin ID | School name | +---------------+---------------+---------------+ | 1 | 1 | Hilbre | | 2 | 1 | Ridgeway | | 3 | 2 | Grammar | | 4 | 2 | Calday | +---------------|---------------+---------------+ From the above example, Administrator Mr James has registered Schools Hibre and Ridgeway, whilst Mr Davies has registered schools Grammar and Calday. Now, for the complicated part that I'm struggling with. Administrators and Students should be interchangeable. For example, Administrator Mr James may 'belong' to any School created by Mr Davies - as may any of his students. I've tried to keep track of Administrators/Students/Schools by using a School members table. SCHOOL MEMBERS +---------------+---------------+---------------+ | *Member type | Member ID | School ID | +---------------+---------------+---------------+ | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | 1 | 2 | | 0 | 2 | 2 | +---------------|---------------+---------------+ * 0 = Student 1 = Administrator Without trying to complicate matters, the table above shows that Mr James and one of his students are both members of a School registered by a different administrator. The query I'm trying to write would be; If I'm logged in as Mr James (or any other administrator), I'd like to query these tables to get a list of students that I've created, students that belong to my schools AND students that belongs to other schools that I'm a member of. I hope that makes sense and I hope I haven't over complicated it - is there's any easier way to handle this data, I'm all ears! EDIT: In the above query, I'd also need a GROUP_CONCAT list of Schools that the student belongs to (if they belong to any) if possible! Thanks for any help