I am enjoying the steep learning curve that is sql after using it in access but have not got stuck again on how to make a many to many query work for me. I have been told a stored procedure may be the way forward.
For the example i am trying to get working i have a persons table and a transport table with a link table in between. If i search for more than one method of transport i get multiple occurrences of each person (one for each method of transport they use) however i would like to only recive one result for each person because as long as i know a person uses that particular transport then it is just the person record i am interested in..
In other words using this example below from access if i do not want to display the criteria i am searching, only the results from the people table how would i do this.
SELECT tblPerson.PersonID, tblPerson.Person
FROM tblPerson INNER JOIN tblLink ON tblPerson.PersonID = tblLink.PersonFID
WHERE (((tblLink.TransportFID)=3)) OR (((tblLink.TransportFID)=1));
FROM dbo.tblPerson AS p
INNER JOIN dbo.tblLink AS l
ON l.PersonFID = p.PersonID
GROUP BY p.PersonID,
HAVING SUM(CASE WHEN l.TransportFID IN (1, 3) THEN 1 ELSE 0 END) >0