SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Many to Many Queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JBRTaylor
Starting Member

United Kingdom
5 Posts

Posted - 06/15/2013 :  04:10:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 06/15/2013 :  09:22:10  Show Profile  Reply with Quote
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

Sweden
30214 Posts

Posted - 06/15/2013 :  09:25:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 06/15/2013 09:26:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/15/2013 :  13:05:40  Show Profile  Reply with Quote

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


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

JBRTaylor
Starting Member

United Kingdom
5 Posts

Posted - 06/16/2013 :  03:28:32  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/16/2013 :  14:21:25  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000