query to list members in table

[TheJuan]

Please help. I would like to build a list of all students with their different organization(s). Every organization representatives would submit their list with this field: Firstname, Familyname, Middle Initial, OrganizationID.

 

How can I list the students in an organization with single and/or multiple memberships?

 

[kickstart]

Hi

 

Table of students (one row per student). Another table of organisations (one row per organisation). And a third table that links them together.

 

Then:-

 

SELECT StudentName

FROM Students

INNER JOIN StudentOrganisations ON Students.Id = StudentOrganisations.studentId

INNER JOIN Organisations ON StudentOrganisations.OrganisationId = Organisations.Id

WHERE Organisations.OrganisationName = "Some Organisations Name"

 

All the best

 

Keith

[TheJuan]

thank you for your time, but that is not what i would like to generate

 

here are my tables

tbl.members :

id

givenname

middleinitial

familyname

orgID

remarks

 

tbl.organization :

orgID

name

incharge

phone

 

what i would like to make is a report regarding name of students who is a member

 

of a centain organization and at the same time list the other organization where

 

he/she affiliate

 

sample:

memberID = 1 ; orgID = 1

memberID = 1 ; orgID = 2

 

memberID = 2 ; orgID = 1

memberID = 2 ; orgID = 3

 

memberID = 3 ; orgID = 2

memberID = 3 ; orgID = 3

memberID = 3 ; orgID = 4

 

memberID = 4 ; orgID = 1

 

memberID = 5 ; orgID = 6

memberID = 5 ; orgID = 3

memberID = 5 ; orgID = 4

 

memberID = 7 ; orgID = 1

memberID = 7 ; orgID = 3

memberID = 7 ; orgID = 4

 

 

when run the query with orgID = 1 this will give a result

 

memberID : 1 ; orgID : 1

memberID : 1 ; orgID : 2 << because he also belong to orgID=1

 

memberID : 2 ; orgID : 1

memberID : 2 ; orgID : 3 << because he also belong to orgID=1

 

memberID : 4 ; orgID : 1

 

memberID : 7 ; orgID : 1 << because he also belong to orgID=1

memberID : 7 ; orgID : 3 << because he also belong to orgID=1

memberID : 7 ; orgID : 4 << because he also belong to orgID=1

 

[kickstart]

Hi

 

Give something like this a try:-

 

SELECT *

(SELECT id

FROM tbl.members m

WHERE orgID = $someId) m1

INNER JOIN tbl.members m2

ON m1.id = m2.id

INNER JOIN tbl.organization o

ON m2.orgID = o.orgID

ORDER BY m2.id, m2.orgID

 

However it seems that you have multiple rows on tbl.members for each person, one row per organisation. Would be best to have a persons table and then another take to link members to organisations. As it is now of someone changed their family name you could have loads of rows to update.

 

All the best

 

Keith

[TheJuan]

Hi

 

i run the query:

SELECT *

(SELECT id

FROM members m

WHERE orgid = '1') m1

INNER JOIN members m2

ON m1.id = m2.id

INNER JOIN organization o

ON m2.orgid = o.orgid

ORDER BY m2.id, m2.orgid;

 

 

---Here is the error message---

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT id

FROM members m

WHERE orgid = '1') m1

INNER JOIN members m2

ON m1.id =' at line 2

 

i get and make orgid = 1

-- still i cannot make it work --

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT id

FROM members m

WHERE orgid = 1) m1

INNER JOIN members m2

ON m1.id = m' at line 2

 

help is greatly appreciated

[kickstart]

Hi

 

Oops, typo. Missed the FROM.

 

i run the query:

SELECT *

FROM (SELECT id

FROM members m

WHERE orgid = '1') m1

INNER JOIN members m2

ON m1.id = m2.id

INNER JOIN organization o

ON m2.orgid = o.orgid

ORDER BY m2.id, m2.orgid;

 

All the best

 

Keith