List brands with >0 bullets

[bulrush]

Mysql 4.1.11

 

I'm having trouble with a join. I need to list all records on the "brands" table which have at least one record on the "bullets" table. However, these tables are linked by 2 fields. This list of brands will be used to make a SELECT box in PHP.

 

Here is the basic layout of the tables.

 

Table: brands

Fields:

bid (key field, autoinc, and links to bullets!brandid)

partid (links to bullets!partid)

brand (varchar(50))

 

Table: bullets

Fields:

bullid (key field, autoinc)

partid (links to brands!partid)

brandid (bigint, links to brands!bid)

bullettext (varchar(255))

 

I'm not real good at joins. Can anyone help me out please?

 

Thanks.

 

[bulrush]

Well, I got the query to run but all records returned show bullets. My DB is arranged like this:

Table: parts

 

For each part there are 1 or more brands. For each brand there are 1 or more bullets. Given a part number stored on table "parts", I need to show which brands have at least one bullet and construct a SELECT list from those records.

 

[bulrush]

Ok, here is my query so far, but I'm getting an error "Unknown column bucnt in WHERE clause". Why do I get the error when I defined what bucnt is?

 

SELECT brands.brand, brands.partid, bullets.bullid, bullets.bullettext, bullets.buorder, 
count(bullets.partid) as bucnt, bullets.partid as bupartid 
FROM brands 
JOIN bullets ON (brands.partid=bullets.partid) 
WHERE (brands.partid=31537) AND (bucnt>0)
GROUP BY brand
ORDER BY brand, partid

[mikosiko]

according to MYSQL manual:

"It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.5.5.4, “Problems with Column Aliases”."

 

but you can try writing your query in this way:

 

SELECT brands.brand, brands.partid, bullets.bullid, bullets.bullettext, bullets.buorder, 
count(bullets.partid) as bucnt, bullets.partid as bupartid 
FROM brands 
JOIN bullets ON (brands.partid=bullets.partid) 
WHERE (brands.partid=31537)
GROUP BY brand
HAVING bucnt > 0
ORDER BY brand, partid

[bulrush]

I will try this out. Thanks.

 

[bulrush]

This is not quite working. A bullet is a line of descriptive text. Each part has one or more bullets associated with it. However, each part can appear under one or more brands. The user needs to be able to copy all bullet text from one brand to another, and change the bullet text as they desire.

 

My task: if the current brand for a given part has no bullets, allow the user to copy bullet text from another brand. List in a SELECT box only brands which are not the current brand, and which have more than zero bullets. This SELECT box contains the brand to copy bullets from. The SELECT box contents is based on the query below.

 

Here is my current query:

    $query2="SELECT brands.brand, brands.partid, bullets.bullid, ".
    "bullets.bullettext, bullets.buorder, ".
    "count(bullets.partid) as bucnt, bullets.partid as bupartid ".
    "FROM brands ".
    "JOIN bullets ON (brands.partid=bullets.partid) ".
    "WHERE (brands.partid=".$partid.") ".
    "AND (brands.brand<>'".$brand."') ".
    "GROUP BY brand ".
    "HAVING bucnt > 0 ".
    "ORDER BY brand, partid ".
    ";";

It correctly excludes the current brand, but it incorrectly lists a brand with zero bullets, which I will call brand "C". Anyone know how to fix this?

 

I double checked my bullets table and there are no bullets for brand "C", but brand "C" appears in my <SELECT> box.