Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Many to Many Queries

Author  Topic 

JBRTaylor
Starting Member

5 Posts

Posted - 2013-06-15 : 04:10:24
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));


Many Thanks in advance
Jon

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-15 : 09:22:10
Use of the EXISTS clause might be the most straightforward option:
SELECT 
tblPerson.PersonID,
tblPerson.Person
FROM
tblPerson
WHERE
EXISTS
(
SELECT * FROM tblLink
WHERE tblLink.TransportFID in (1,3)
AND tblPerson.PersonID = tblLink.PersonFID
);
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-15 : 09:25:42
[code]SELECT p.PersonID,
p.Person
FROM dbo.tblPerson AS p
INNER JOIN (
SELECT DISTINCT PersonFID
FROM dbo.tblLink
WHERE TransportFID IN (1, 3)
) AS w ON w.PersonFID = p.PersonID[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-15 : 13:05:40
[code]
SELECT p.PersonID,
p.Person
FROM dbo.tblPerson AS p
INNER JOIN dbo.tblLink AS l
ON l.PersonFID = p.PersonID
GROUP BY p.PersonID,
p.Person
HAVING SUM(CASE WHEN l.TransportFID IN (1, 3) THEN 1 ELSE 0 END) >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JBRTaylor
Starting Member

5 Posts

Posted - 2013-06-16 : 03:28:32
Hi Guys

Thanks for your help that is great. I have tried using the code in PHPRunner which i am using to build my front end and it didn't work for me but within SQL studio it worked great.

I think i need to go away and learn how to write PHP, wow that is gonna be a challenge. Why do i do it to myself, lol. Thanks for your help and no doubt i'll be back for more advice.

Thanks
Jon
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-16 : 14:21:25
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -