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-gcdmg-mysql=m.gmane.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php