MySQL Problem

Sorcy

I have two tables, one with Customer IDs, one with Products.

Looks like this:

Table exhibitor:

ID (bigint unsigned autoincrement)
Name (Varchar 100)
...several other fields


Table Products:
ID (bigint unsigned autoincrement)
Productnumber (Varchar 10)
Productname (Varchar 100)

I also have a table to map the n-to-n connections - build like this:

Table Exhibitor2Products
ID (bigint unsigned autoincrement)
exhibitorID (maps to exhibitor.ID)
Productnumber (! maps to products.Productnumber, NOT ID)

So far I've use this in several different ways, to display which exhibitor has which Products and also which Products will be shown by which exhibitor (it's a trade fair website). Worked wonderfully ... but now I updated the exhibitor2Products table with new data from my client, and all of the sudden the connection between the exhibitor2products table and the productstable is dead. Even so I can clearly see that the numbers are the same (1.100 for example) fucking MySQL will no longer show me anything, when I try something like

SELECT
`exhibitor2product`.`exhibitorID`,
`productgroups`.`productgroup`
FROM
`exhibitor2product`
Inner Join `productgroups` ON `exhibitor2product`.`productID` = `productgroups`.`id`
GROUP BY
`productgroups`.`id`

Anyone has an idea whats up with that? All I did was update the data, the querys worked fine before.

Daenris

Is the productgroups table the same as the products table? If so, shouldn't exhibitor2product.productID = productgroups.id actually be exhibitor2product.productID = productgroups.productnumber since you specifically say that you want the productnumber field in exhibitor2product to map to the product number, not the id? I know you said you didn't change the query, but that looks odd.

CmdPrompt

I agree with Daenris, are you absolutely certain that the queries are no longer working due to just adding new rows, or was something else changed around that time?

Also, why are you using the GROUP BY clause? You aren't using any aggregate functions.

Sorcy

Argh, sorry, that's all because the tables are actually in german and I tried to translate it on the fly to make it easier for english speakers. The grouping is there, because in the real request I'm actually counting the numbers of exhibitors per productgroup (and yes, products = productgroups).

Here's the real query, without any translation to fuck it up:

SELECT PG.pid, PG.id, produktgruppe AS name, COUNT(A2P.AusstellerID) as anzahl
FROM produktgruppen AS PG
LEFT JOIN aussteller2produkten AS A2P ON ProduktID = PG.ID
WHERE 1
GROUP BY PG.ID ORDER BY PG.ID

And yes, nothing else was changed. It worked (and has been working for quite some time), I changed the A2P tables data and it stopped working...