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" <[EMAIL PROTECTED]>
To: "Mikhail Entaltsev" <[EMAIL PROTECTED]>
Cc: "MySQL" <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
> To: "Mikhail Entaltsev" <[EMAIL PROTECTED]>
> Cc: "MySQL" <[EMAIL PROTECTED]>
> 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" <[EMAIL PROTECTED]>
> > To: "Arul" <[EMAIL PROTECTED]>; "Ralf Narozny"
> <[EMAIL PROTECTED]>
> > Cc: "MySQL" <[EMAIL PROTECTED]>
> > 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" <[EMAIL PROTECTED]>
> > > To: "Ralf Narozny" <[EMAIL PROTECTED]>
> > > Cc: "MySQL" <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]>
> > > > To unsubscribe, e-mail
> > > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
