Re: How to find out common records with Where clause
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 9 Jan 2009 07:26:03 -0800 (PST)
Message-ID: <26aca96e-e489-447c-8637-da36c2d25b31_at_l39g2000yqn.googlegroups.com>
On Jan 9, 9:06 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Jan 9, 7:08 am, Sanjeev <sanjeev.atvan..._at_gmail.com> wrote:
>
>
>
>
>
> > Dear Gurus,
>
> > I have "VISITOR" table as follows.
>
> > VisitNo EmpName Address
> > --------------------------------------------
> > 1 Sanjeev Mumbai
> > 2 Rajeev New-Mumbai
> > 3 Shailesh Mumbai
> > 4 Ramesh New-Mumbai
>
> > 5 Sanjeev Pune-1
> > 6 Rajeev Pune-2
> > 7 Shailesh Pune-3
> > 8 Shreyas Pune-4
>
> > 9 Sanjeev Delhi
> > 10 Rajeev New-Delhi
> > 11 Shailesh Delhi
> > 12 Shreyas New-Delhi
>
> > If I give location as Mumbai, Pune and Delhi i.e. location based
> > pattern matching (LIKE operator) then
> > query should retrieve record as Sanjeev,Rajeev and Shailesh
> > i.e.
> > all employees those are present in all given locations (Mumbai, Pune
> > and Delhi).
> > query shouldn't retrieve Ramesh, Shreyas because they are not present
> > in all given location.
>
> > I tried with "OR" operator but it did not work.
>
> > Could any one help me in above.
>
> > Thanking in advance
> > Sanjeev
>
> Hi Sanjeev,
>
> Can you post what you have already tried, including DDL for the table
> creates?
>
> Regards,
>
> Steve- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 9 Jan 2009 07:26:03 -0800 (PST)
Message-ID: <26aca96e-e489-447c-8637-da36c2d25b31_at_l39g2000yqn.googlegroups.com>
On Jan 9, 9:06 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Jan 9, 7:08 am, Sanjeev <sanjeev.atvan..._at_gmail.com> wrote:
>
>
>
>
>
> > Dear Gurus,
>
> > I have "VISITOR" table as follows.
>
> > VisitNo EmpName Address
> > --------------------------------------------
> > 1 Sanjeev Mumbai
> > 2 Rajeev New-Mumbai
> > 3 Shailesh Mumbai
> > 4 Ramesh New-Mumbai
>
> > 5 Sanjeev Pune-1
> > 6 Rajeev Pune-2
> > 7 Shailesh Pune-3
> > 8 Shreyas Pune-4
>
> > 9 Sanjeev Delhi
> > 10 Rajeev New-Delhi
> > 11 Shailesh Delhi
> > 12 Shreyas New-Delhi
>
> > If I give location as Mumbai, Pune and Delhi i.e. location based
> > pattern matching (LIKE operator) then
> > query should retrieve record as Sanjeev,Rajeev and Shailesh
> > i.e.
> > all employees those are present in all given locations (Mumbai, Pune
> > and Delhi).
> > query shouldn't retrieve Ramesh, Shreyas because they are not present
> > in all given location.
>
> > I tried with "OR" operator but it did not work.
>
> > Could any one help me in above.
>
> > Thanking in advance
> > Sanjeev
>
> Hi Sanjeev,
>
> Can you post what you have already tried, including DDL for the table
> creates?
>
> Regards,
>
> Steve- Hide quoted text -
>
> - Show quoted text -
As Steve requested a create DDL with inserts would be nice but as you mentioned using OR conditions and then said that your query did not work I will mention a common mistake or issue.
When you use an OR and also have other conditions you almost always need to group the OR conditions inside parenthesis:
AND ( col = 'x' or col = 'y' or col = 'z' ) AND ...
Another way to do this would be to use an IN clause
and col in ('x','y','z')
HTH -- Mark D Powell -- Received on Fri Jan 09 2009 - 09:26:03 CST