Many to Many Queries - SQL Server Forums

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
	);

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"

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources