Hi.
On Mon, Dec 03, 2001 at 01:22:30PM +0100, [EMAIL PROTECTED] wrote:
> Dear List reader and especially list writer,
>
> I usually get around the missing of subselects, but now i really
> don't know how to do without whats impossible at the moment.. In a
> rather big query involving 7 tables, one of the tables has to meet 5
> conditions. Can somebody tell me how to do this within a 3.23
> environment, tia.
>
> Maarten
[...]
> e) the table has a maximum of 2 rows with X in column 2
[...]
> The problem is E;
> I want to have a max count of rows which meet the A -condition not bigger then
>2
>
> <WHISH>
> select a1.DOMAINNAME
> from
> CUSTOM_ARECORDS as a1,
> CUSTOM_NS as n1,
> CUSTOM_NS as n2
> where
> a1.DOMEINNAME=n1.DOMEINNAME and // Point A
> n1.DOMEINNAME=n2.DOMEINNAME and
>
> a1.CUSTOM_ARECORDTARGET='xx.xx.36.18' // Other table
> and
> n1.CUSTOM_NAMESERVER='ns1.isp.com.' // point B
> and
> n1.CUSTOM_PRIMARY='1' // point C
> and
> n2.CUSTOM_NAMESERVER='ns2.isp.com.' // point D
> and ( select count(*) //
>point E by impossible subselect
> from CUSTOM_NS as sn1,
> where
> sn1.DOMAINNAME=a1.DOMAINNAME ) < 3 ;
> <WISH>
I have not tried it, but I think it should work if I understand you correctly:
select
a1.DOMAINNAME
from
CUSTOM_ARECORDS as a1,
CUSTOM_NS as n1,
CUSTOM_NS as n2,
CUSTOM_NS as sn1
where
a1.DOMEINNAME=n1.DOMEINNAME and
n1.DOMEINNAME=n2.DOMEINNAME and
a1.CUSTOM_ARECORDTARGET='xx.xx.36.18' and
n1.CUSTOM_NAMESERVER='ns1.isp.com.' and
n1.CUSTOM_PRIMARY='1' and
n2.CUSTOM_NAMESERVER='ns2.isp.com.' and
sn1.DOMAINNAME=a1.DOMAINNAME
group by
a1.DOMAINNAME
having
COUNT(a1.DOMAINNAME) < 3
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
