Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: mk sharma
  Where is mk sharma?
 mumbai
 India
 mk sharma
 Tags
Subject: RE: Select Sql
Thread ID: 416083 Message ID: 416113 # Views: 38 # Ratings: 5
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Sunday, December 21, 2014 10:58:37 PM         
   


> I want only records that persons have same film id.
>
>
> 
> ** In below example only starting 3 records and last 2 records have the same film_id for both the persons
> 
> Create Cursor junk1 (Record_id i, film_id i, person_id i, prof_id i)
> Insert Into junk1 Values(1, 1, 45, 1)
> Insert Into junk1 Values(2, 1, 45, 2)
> Insert Into junk1 Values(3, 1, 38, 1)
> 
> Insert Into junk1 Values(4, 2, 45, 1)
> Insert Into junk1 Values(5, 2, 45, 2)
> 
> Insert Into junk1 Values(6, 3, 38, 1)
> Insert Into junk1 Values(7, 3, 38, 2)
> 
> Insert Into junk1 Values(8, 4, 66, 1)
> Insert Into junk1 Values(9, 4, 66, 2)
> 
> Insert Into junk1 Values(10, 5, 45, 1)
> Insert Into junk1 Values(11, 5, 38, 2)
> 
> 
> *** Please Suggest a Select Sql for correct result, i am using below code but giving Wrong result
> 
> Select * From junk1 Where film_id In(Select film_id From junk1 Where person_id In(45, 38))
> 
> ** Showing records 1,2,3,4,5,6,7,10,11  but i need only records 1,2,3,10,11
> 

>
> Warm Regards,
> mk.


SELECT * FROM Junk1 WHERE 1 < (SELECT COUNT(DISTINCT person_id) ;
FROM Junk1 Junk2 WHERE Junk1.film_id=Junk2.film_id)

Record_id  	Film_id    	Person_id  	Prof_id    	
1 1 45 1
2 1 45 2
3 1 38 1
10 5 45 1
11 5 38 2


-Anders

ENTIRE THREAD

Select Sql Posted by mk sharma @ 12/21/2014 6:32:43 AM
RE: Select Sql Posted by Stefan Wuebbe @ 12/21/2014 6:53:37 AM
RE: Select Sql Posted by mk sharma @ 12/21/2014 7:02:21 AM
RE: Select Sql Posted by Stefan Wuebbe @ 12/21/2014 7:41:31 AM
RE: Select Sql Posted by mk sharma @ 12/21/2014 8:10:07 AM
RE: Select Sql Posted by Vilhelm-Ion Praisach @ 12/21/2014 12:12:33 PM
RE: Select Sql Posted by mk sharma @ 12/21/2014 5:24:53 PM
RE: Select Sql Posted by Anders Altberg @ 12/21/2014 10:58:37 PM