Help tuning a very poor SQL query

[eluser]Leon Stafford[/eluser]
[quote author="jedd" date="1245273001"]Okay .. rough draft absent further understanding is this:

Code:

SELECT *
    FROM kg_compounds
    LEFT JOIN kg_progress ON kg_progress.compound=kg_compounds.id
    WHERE grade='$level'
    AND kg_progress.compound_progress=1

UNTESTED, mind. Perhaps something for you to start playing with, at least.

Can I suggest that for foreign keys - for example your kg_progress.compound - that you call them the other table name with a _id suffix.

So rather than kg_progress.compound you'd call it kg_progress.compound_id

I'd also suggest that you try to avoid having column names that are the same as table names - it's just plain confusing, and hints at a badly considered / designed schema. Often renaming the column as 'name' or 'description' is sufficiently descriptive.[/quote]

Thanks again Jedd,

There goes my sleep tonight, I will try and get this happening...

I totally get you with the bad naming conventions used.

That said, is it technically possible to do any kind of JOINs even if the tables aren't named well, ie using AS to cast it to a different name for the query instance?

I got errors on that join and fiddled a bit but haven't got it yet.

Also, I am trying to get all the rows, EXCLUDING the ones marked "1" in the kg_progress table, can I still use JOIN xxxxxx = 1 in that case or !=1 or do I need a different operand than JOIN?

I regret not having been more interested in MS Access in high school computer class

Cheers,

Leon