[SOLVED] Interesting Query idea

[SirChick]

I was wondering if this can be done with a query.. lets say we have a table with a field called name.

 

So the list is:

 

Cheese

Bacon

Cheese

Cheese

Sausage

Bacon

Bacon

 

Now im trying to see if you can make a query so that the query will find only the name field where by there is less than 3 records with that name....

 

So in this particular situation if the query is possible only Sausage should appear:

 

1.Cheese

1.Bacon

2.Cheese

3.Cheese

1.Sausage

2.Bacon

3.Bacon

 

Can that be done =/ ive not made a query this complex before so worth asking first..cos I got no idea..

[MadTechie]

Yes it can be done..

 

i'm not sure if you asking how.. but i'll tell you this.. your use a self join and COUNT(*) 

[SirChick]

Well I was going to try work it out but now you mention the self join and count i've never used one so I think you may need to show me an example if thats ok ?

[MadTechie]

OK you can do it without a self join..

Code if below you need it,

 

Just quote this message to see it..

 

SELECT *, count(names) as C

FROM `TEST`

GROUP BY names

HAVING C < 3

 

 

or Lookup COUNT & GROUP

[SirChick]

may i ask what the "C" is about ? =/

 

[corbin]

If you don't alias the count, you can't refer to it later in the query.  Well, I guess you could but it would be a pain....

[MadTechie]

What corbin said.. makes life easier!

[SirChick]

I gave it a try dont think i got it right lol

 

$CountNames = ("SELECT *, count(BusinessType) as C FROM `businesses` GROUP BY BusinessType HAVING C < 3");
$findbusinessresult = @mysql_query($CountNames) or die(mysql_error());

Echo $findbusinessresult;

 

No echo happens though =/

 

Just so you know on this the object is to find only the business types where by there are only 2 or less currently owned.

 

So if there was 3 hardware shops .. in the table.. that would suggest there are 3 already owned and so it would not show the field "businesstype" for that business.

[MadTechie]

<?php
$CountNames = ("SELECT *, count(BusinessType) as C FROM `businesses` GROUP BY BusinessType HAVING C < 3");
$findbusinessresult = @mysql_query($CountNames) or die(mysql_error());
$row = mysql_fetch_assoc($findbusinessresult); //<--missing
echo $row['BusinessType'];
?>

[SirChick]

MadTechie thankyou for your help and yours also corbin