>My table is defined as
>TABLE PERSON
>id, int
>Name varchar(500)
>
>Table PlacesOfTravel
>Personid(fk from table PERSON)
>Place
>
>sample data for PERSON
>1 Manmohan singh
>2 Sonia Gandhi
>
>sample data for PlacesOfTravel
>1 New Delhi
>1 New York
>1 Bangalore
>1 Hyderabad
>1 Chennai
>2 New Delhi
>2 Bangalore
>
>QUERIES THAT I HAVE TO WROTE
>`````````````````````````````````````````````````````````
>
>-- Print name of all persons who have visited both Bangalore and New
>Delhi
>-- Print name of all persons who have visited more than 3 cities
>
>Please help.

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:0odig25us91e61bml1478s5fh665lnvq58@4ax.com...
> SELECT P.PersonID, P.Name
>   FROM Person as P
>   JOIN PlacesOfTravel as T
>     on P.PersonID = T.PersonID
>  WHERE T.Place IN ('Bangalore', 'New Delhi')
>  GROUP BY P.PersonID, P.Name
> HAVING COUNT(T.Place) = 2
>
> SELECT P.PersonID, P.Name, count(*) as Visits
>   FROM Person as P
>   JOIN PlacesOfTravel as T
>     on P.PersonID = T.PersonID
>  GROUP BY P.PersonID, P.Name
> HAVING COUNT(*) > 3
>
> Roy Harvey
> Beacon Falls, CT
>
> On 14 Sep 2006 03:09:48 -0700, "New to SQL"
> <write_to_ashut***@hotmail.com> wrote:
>
> >My table is defined as
> >TABLE PERSON
> >id, int
> >Name varchar(500)
> >
> >Table PlacesOfTravel
> >Personid(fk from table PERSON)
> >Place
> >
> >sample data for PERSON
> >1 Manmohan singh
> >2 Sonia Gandhi
> >
> >sample data for PlacesOfTravel
> >1 New Delhi
> >1 New York
> >1 Bangalore
> >1 Hyderabad
> >1 Chennai
> >2 New Delhi
> >2 Bangalore
> >
> >QUERIES THAT I HAVE TO WROTE
> >`````````````````````````````````````````````````````````
> >
> >-- Print name of all persons who have visited both Bangalore and New
> >Delhi
> >-- Print name of all persons who have visited more than 3 cities
> >
> >Please help.

>The one problem I have noticed with most of the relational division
>examples, is they assume unique entries in the tables.  What if the person
>visited Bangalore 4 times and New Delhi once, and all 5 were recorded in the
>tables?
>
>If this is possible, then a distinct or agregate would be required when
>checking PlacesOfTravel.
>(select distinct personID, place from PlacesOfTravel) as T
>
>If, however, we are only tracking where a person went at least once, and not
>having seperate entires for return trips, then the solution below is ideal.
>
>"Roy Harvey" <roy_har***@snet.net> wrote in message
>news:0odig25us91e61bml1478s5fh665lnvq58@4ax.com...
>> SELECT P.PersonID, P.Name
>>   FROM Person as P
>>   JOIN PlacesOfTravel as T
>>     on P.PersonID = T.PersonID
>>  WHERE T.Place IN ('Bangalore', 'New Delhi')
>>  GROUP BY P.PersonID, P.Name
>> HAVING COUNT(T.Place) = 2
>>
>> SELECT P.PersonID, P.Name, count(*) as Visits
>>   FROM Person as P
>>   JOIN PlacesOfTravel as T
>>     on P.PersonID = T.PersonID
>>  GROUP BY P.PersonID, P.Name
>> HAVING COUNT(*) > 3
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On 14 Sep 2006 03:09:48 -0700, "New to SQL"
>> <write_to_ashut***@hotmail.com> wrote:
>>
>> >My table is defined as
>> >TABLE PERSON
>> >id, int
>> >Name varchar(500)
>> >
>> >Table PlacesOfTravel
>> >Personid(fk from table PERSON)
>> >Place
>> >
>> >sample data for PERSON
>> >1 Manmohan singh
>> >2 Sonia Gandhi
>> >
>> >sample data for PlacesOfTravel
>> >1 New Delhi
>> >1 New York
>> >1 Bangalore
>> >1 Hyderabad
>> >1 Chennai
>> >2 New Delhi
>> >2 Bangalore
>> >
>> >QUERIES THAT I HAVE TO WROTE
>> >`````````````````````````````````````````````````````````
>> >
>> >-- Print name of all persons who have visited both Bangalore and New
>> >Delhi
>> >-- Print name of all persons who have visited more than 3 cities
>> >
>> >Please help.
>

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:25ijg21elosvjf3ujkpu9timmfd4b9kb7i@4ax.com...
> Good points, Jim.  However handling the count when there are multiple
> rows does not require a derived table, though that would work fine.
> All the is required is to change COUNT(T.Place) in the first query to
> COUNT(DISTINCT T.Place), and in the second query change COUNT(*) to
> the same thing, COUNT(DISTINCT T.Place).
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 14 Sep 2006 11:17:08 -0400, "Jim Underwood"
> <james.underwoodATfallonclinic.com> wrote:
>
> >The one problem I have noticed with most of the relational division
> >examples, is they assume unique entries in the tables.  What if the
person
> >visited Bangalore 4 times and New Delhi once, and all 5 were recorded in
the
> >tables?
> >
> >If this is possible, then a distinct or agregate would be required when
> >checking PlacesOfTravel.
> >(select distinct personID, place from PlacesOfTravel) as T
> >
> >If, however, we are only tracking where a person went at least once, and
not
> >having seperate entires for return trips, then the solution below is
ideal.
> >
> >"Roy Harvey" <roy_har***@snet.net> wrote in message
> >news:0odig25us91e61bml1478s5fh665lnvq58@4ax.com...
> >> SELECT P.PersonID, P.Name
> >>   FROM Person as P
> >>   JOIN PlacesOfTravel as T
> >>     on P.PersonID = T.PersonID
> >>  WHERE T.Place IN ('Bangalore', 'New Delhi')
> >>  GROUP BY P.PersonID, P.Name
> >> HAVING COUNT(T.Place) = 2
> >>
> >> SELECT P.PersonID, P.Name, count(*) as Visits
> >>   FROM Person as P
> >>   JOIN PlacesOfTravel as T
> >>     on P.PersonID = T.PersonID
> >>  GROUP BY P.PersonID, P.Name
> >> HAVING COUNT(*) > 3
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On 14 Sep 2006 03:09:48 -0700, "New to SQL"
> >> <write_to_ashut***@hotmail.com> wrote:
> >>
> >> >My table is defined as
> >> >TABLE PERSON
> >> >id, int
> >> >Name varchar(500)
> >> >
> >> >Table PlacesOfTravel
> >> >Personid(fk from table PERSON)
> >> >Place
> >> >
> >> >sample data for PERSON
> >> >1 Manmohan singh
> >> >2 Sonia Gandhi
> >> >
> >> >sample data for PlacesOfTravel
> >> >1 New Delhi
> >> >1 New York
> >> >1 Bangalore
> >> >1 Hyderabad
> >> >1 Chennai
> >> >2 New Delhi
> >> >2 Bangalore
> >> >
> >> >QUERIES THAT I HAVE TO WROTE
> >> >`````````````````````````````````````````````````````````
> >> >
> >> >-- Print name of all persons who have visited both Bangalore and New
> >> >Delhi
> >> >-- Print name of all persons who have visited more than 3 cities
> >> >
> >> >Please help.
> >