Re: Problem with a query

Hi Arul,

> What i meant to say is that My First Query returns 3 rows satisfying two
> conditions Like CI.IndustryID IN (2,3) and Count(C.CompanyID) = 2
>
> So when i add another OR say (CI.IndustryID IN (2,3) OR C.b2b = 'N') and
> Count(C.CompanyID) = 2
>
> I guess it should aways have the possibilty of returning more rows or the
> same rows..I wonder how the number of rows returned is decreased with an
OR
> Condition

Don't forget that SQL server will check HAVING clause after all others.
I mean that query plan will be:
1. Select those rows for which WHERE clause = TRUE
2. Group by them.
3. Take only those rows for which HAVING clause =TRUE.

Let's take my example and your queries again...

C.CompanyID    C.B2b    CI.IndustryID
64                        Y                2
64                        Y                3
77                        N                2
77                        N                3
77                        N                5
78                        Y                2
78                        Y                3


SELECT C.Companyid,C.B2b
FROM  Company C ,Company C1 , Company_Industries CI,
              Company_Type_Details CTD,Users U,User_Type_Details UTD
Where   C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid
              AND UTD.UserId=U.UserId AND UTD.User_Typeid=2
              AND CTD.Company_Typeid=3 AND CTD.App_Status='APP'
              AND U.UserID <> 2 AND C.Company_App_Status='APP'
              AND C.Company_Status='ACT'
              AND C.CompanyID = CI.CompanyID
              AND C.CompanyID = C1.CompanyID
              AND (CI.IndustryID IN (2,3) )
Group By
              C.CompanyID,C.B2b
HAVING
             Count(C.CompanyID) = 2

After checking (CI.IndustryID IN (2,3)) we will have rows:
C.CompanyID    C.B2b    CI.IndustryID
64                        Y                2
64                        Y                3
77                        N                2
77                        N                3
78                        Y                2
78                        Y                3

Then group by C.CompanyID,C.B2b and Count(C.CompanyID)

C.CompanyID    C.B2b    Count(C.CompanyID)
64                        Y                2
77                        N                2
78                        Y                2

after that server will take only record that have Count(C.CompanyID) = 2. In
this case it will take all 3 records.

Next query:
SELECT C.Companyid,C.B2b
FROM  Company C ,Company C1 , Company_Industries CI,
              Company_Type_Details CTD,Users U,User_Type_Details UTD
Where   C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid
              AND UTD.UserId=U.UserId AND UTD.User_Typeid=2
              AND CTD.Company_Typeid=3 AND CTD.App_Status='APP'
              AND U.UserID <> 2 AND C.Company_App_Status='APP'
              AND C.Company_Status='ACT'
              AND C.CompanyID = CI.CompanyID
              AND C.CompanyID = C1.CompanyID
              AND (CI.IndustryID IN (2,3) OR C.B2b = 'N')
Group By
              C.CompanyID,C.B2b
HAVING
             Count(C.CompanyID) = 2

After checking (CI.IndustryID IN (2,3) OR C.B2b = 'N') we will have rows:
C.CompanyID    C.B2b    CI.IndustryID
64                        Y                2
64                        Y                3
77                        N                2
77                        N                3
77                        N                5
78                        Y                2
78                        Y                3

Then group by C.CompanyID,C.B2b and Count(C.CompanyID)
C.CompanyID    C.B2b    Count(C.CompanyID)
64                        Y                2
77                        N                3
78                        Y                2

after that server will take only record that have Count(C.CompanyID) = 2. In
this case it will take only:
C.CompanyID    C.B2b    Count(C.CompanyID)
64                        Y                2
78                        Y                2

Next query:
SELECT C.Companyid,C.B2b
FROM  Company C ,Company C1 , Company_Industries CI,
              Company_Type_Details CTD,Users U,User_Type_Details UTD
Where   C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid
              AND UTD.UserId=U.UserId AND UTD.User_Typeid=2
              AND CTD.Company_Typeid=3 AND CTD.App_Status='APP'
              AND U.UserID <> 2 AND C.Company_App_Status='APP'
              AND C.Company_Status='ACT'
              AND C.CompanyID = CI.CompanyID
              AND C.CompanyID = C1.CompanyID
              AND (CI.IndustryID IN (2,3) AND C.B2b = 'N')
Group By
              C.CompanyID,C.B2b
HAVING
             Count(C.CompanyID) = 2

After checking (CI.IndustryID IN (2,3) AND C.B2b = 'N') we will have rows:
C.CompanyID    C.B2b    CI.IndustryID
77                        N                2
77                        N                3

Then group by C.CompanyID,C.B2b and Count(C.CompanyID)
C.CompanyID    C.B2b    Count(C.CompanyID)
77                        N                2

after that server will take only record that have Count(C.CompanyID) = 2. In
this case it will take:
C.CompanyID    C.B2b    Count(C.CompanyID)
77                        N                2

Best regards,
Mikhail.



----- Original Message -----
From: "Arul" <arulkumar@xxxxxxxxxxxxx>
To: "Mikhail Entaltsev" <mike_lynx@xxxxxxx>
Cc: "MySQL" <mysql@xxxxxxxxxxxxxxx>
Sent: Saturday, June 29, 2002 07:32
Subject: Re: Problem with a query


> Hi Mikhail
>
> What i meant to say is that My First Query returns 3 rows satisfying two
> conditions Like CI.IndustryID IN (2,3) and Count(C.CompanyID) = 2
>
> So when i add another OR say (CI.IndustryID IN (2,3) OR C.b2b = 'N') and
> Count(C.CompanyID) = 2
>
> I guess it should aways have the possibilty of returning more rows or the
> same rows..I wonder how the number of rows returned is decreased with an
OR
> Condition
>
> Regards,
> -Arul
>
>
> ----- Original Message -----
> From: "Arul" <arulkumar@xxxxxxxxxxxxx>
> To: "Mikhail Entaltsev" <mike_lynx@xxxxxxx>
> Cc: "MySQL" <mysql@xxxxxxxxxxxxxxx>
> Sent: Saturday, June 29, 2002 10:32 AM
> Subject: Re: Problem with a query
>
>
> > No Mikhail
> >
> > I dont think i can agree with you .
> > If you could see my first query which returned 3 rows also had a Having
> > Clause.
> > I just added an OR condition inside the query which should always
increase
> > the Number of Rows Returned..Am i correct..Check the Query plzz..
> >
> > > > SELECT C.Companyid,C.B2b
> > > > FROM  Company C ,Company C1 , Company_Industries CI,
> > > >              Company_Type_Details CTD,Users U,User_Type_Details UTD
> > > > Where   C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid
> > > >              AND UTD.UserId=U.UserId AND UTD.User_Typeid=2
> > > >              AND CTD.Company_Typeid=3 AND CTD.App_Status='APP'
> > > >              AND U.UserID <> 2 AND C.Company_App_Status='APP'
> > > >              AND C.Company_Status='ACT'
> > > >              AND C.CompanyID = CI.CompanyID
> > > >              AND C.CompanyID = C1.CompanyID
> > > >              AND (CI.IndustryID IN (2,3) )
> > > > Group By
> > > >              C.CompanyID,C.B2b
> > > > HAVING
> > > >              Count(C.CompanyID) = 2
> > > >
> > > >
> > > >
> > > > This Query returns 3 rows.
> > > >
> > > >         CompanyID        B2B
> > > >                 64                Y
> > > >                 77                N
> > > >                 78                Y
> > > >
> > > > Then i thought of checking B2b = 'N' with an OR Condition.So My
Query
> > > became
> > > > like this
> > > >
> > > > SELECT C.Companyid,C.B2b
> > > > FROM  Company C ,Company C1 , Company_Industries CI,
> > > >              Company_Type_Details CTD,Users U,User_Type_Details UTD
> > > > Where   C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid
> > > >              AND UTD.UserId=U.UserId AND UTD.User_Typeid=2
> > > >              AND CTD.Company_Typeid=3 AND CTD.App_Status='APP'
> > > >              AND U.UserID <> 2 AND C.Company_App_Status='APP'
> > > >              AND C.Company_Status='ACT'
> > > >              AND C.CompanyID = CI.CompanyID
> > > >              AND C.CompanyID = C1.CompanyID
> > > >              AND (CI.IndustryID IN (2,3) OR C.B2b = 'N' )
> > > > Group By
> > > >              C.CompanyID,C.B2b
> > > > HAVING
> > > >              Count(C.CompanyID) = 2
> > > >
> > > >
> > > > So in Theory this Query Should return more results if the B2b is 'N'
> > Since
> > > > this is an OR Query. But I got only two rows.The Result was
> > > >     CompanyID        B2B
> > > >             64                Y
> > > >             78                Y
> > > >
> > > > What happened to Company 77 whose B2B was 'N'
> > > >
> > > > Instead if i put an AND instead of OR  , i get the result what i
could
> > > judge
> > > > ie:
> > > >     CompanyID        B2B
> > > >             77                N
> > > >
> > > > Can u please explain me what's happenning
> > > >
> >
> >
> > Regards,
> > -Arul
> > ----- Original Message -----
> > From: "Mikhail Entaltsev" <mike_lynx@xxxxxxx>
> > To: "Arul" <arulkumar@xxxxxxxxxxxxx>; "Ralf Narozny"
> <rnarozny@xxxxxxxxxxx>
> > Cc: "MySQL" <mysql@xxxxxxxxxxxxxxx>
> > Sent: Friday, June 28, 2002 8:17 PM
> > Subject: Re: Problem with a query
> >
> >
> > > Arul,
> > >
> > > everything is correct. Let's assume you have this info:
> > >
> > >     C.CompanyID    C.B2b    CI.IndustryID
> > >     64                        Y                2
> > >     64                        Y                3
> > >     77                        N                2
> > >     77                        N                3
> > >     77                        N                5
> > >     78                        Y                2
> > >     78                        Y                3
> > >
> > > > SELECT C.Companyid,C.B2b
> > > > FROM  Company C ,Company C1 , Company_Industries CI,
> > > >              Company_Type_Details CTD,Users U,User_Type_Details UTD
> > > > Where   C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid
> > > >              AND UTD.UserId=U.UserId AND UTD.User_Typeid=2
> > > >              AND CTD.Company_Typeid=3 AND CTD.App_Status='APP'
> > > >              AND U.UserID <> 2 AND C.Company_App_Status='APP'
> > > >              AND C.Company_Status='ACT'
> > > >              AND C.CompanyID = CI.CompanyID
> > > >              AND C.CompanyID = C1.CompanyID
> > > >              AND (CI.IndustryID IN (2,3) OR C.B2b = 'N' )
> > > > Group By
> > > >              C.CompanyID,C.B2b
> > > > HAVING
> > > >              Count(C.CompanyID) = 2
> > >
> > > It will check (CI.IndustryID IN (2,3) OR C.B2b = 'N' ) after that
count
> > > number of record and
> > > after that take only that rows where Count(C.CompanyID) = 2.
> > > So we will loose C.CompanyID = 77, because Count(C.CompanyID) will be
3.
> > > The same story will be if you will substitute OR with AND.
> > >
> > > Best regards,
> > > Mikhail.
> > >
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Arul" <arulkumar@xxxxxxxxxxxxx>
> > > To: "Ralf Narozny" <rnarozny@xxxxxxxxxxx>
> > > Cc: "MySQL" <mysql@xxxxxxxxxxxxxxx>
> > > Sent: Friday, June 28, 2002 3:53 PM
> > > Subject: Problem with a query
> > >
> > >
> > > > Hiho...
> > > >
> > > > This one is very confusing for me...
> > > >
> > > > Note this Query : The one in Red is Important
> > > >
> >
> > > > Regards,
> > > > -Arul
> > > >
> > > > sql , query
> > > >
> > > >
> > > >
> > >
> ---------------------------------------------------------------------
> > > > Before posting, please check:
> > > >    http://www.mysql.com/manual.php   (the manual)
> > > >    http://lists.mysql.com/           (the list archive)
> > > >
> > > > To request this thread, e-mail <mysql-thread113305@xxxxxxxxxxxxxxx>
> > > > To unsubscribe, e-mail
> > > <mysql-unsubscribe-mike_lynx=smtp.ru@xxxxxxxxxxxxxxx>
> > > > Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
> > > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > >    http://www.mysql.com/manual.php   (the manual)
> > >    http://lists.mysql.com/           (the list archive)
> > >
> > > To request this thread, e-mail <mysql-thread113310@xxxxxxxxxxxxxxx>
> > > To unsubscribe, e-mail
> > <mysql-unsubscribe-arulkumar=calsoft.co.in@xxxxxxxxxxxxxxx>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/manual.php   (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <mysql-thread113378@xxxxxxxxxxxxxxx>
> > To unsubscribe, e-mail
> <mysql-unsubscribe-arulkumar=calsoft.co.in@xxxxxxxxxxxxxxx>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread113386@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail 
<mysql-unsubscribe-gcdmg-mysql=m.gmane.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php