Re: workaround count AND the lack of subqueries

[Benjamin Pflugmann <benjamin-mysql@xxxxxxxxxxxx>]

Hi.

On Mon, Dec 03, 2001 at 01:22:30PM +0100, maarten@xxxxxxxxxxx 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.

-- 
benjamin-mysql@xxxxxxxxxxxx

---------------------------------------------------------------------
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-thread92942@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php