Hello All.

I am having a bit of trouble with a query I am making for a web site that references a music collection.

I have the following table structure:

table records
--- id (unique record id)
--- artistname (name of the recording artist(s))
--- title (title of the record)
--- price (price of the record)
--- rcon (The condition of the record an integer that is linked to the condition table)
--- scon (The condition of the sleeve an integer that is linked to the condition table)
--- description (a short description of the record)
--- label (the label the record was recorded on an integer that is linked to the labels table)

table labels
--- labelid (unique identifier of the label)
--- labelname (The actual name of the label)
--- labelnotes (Any notes regarding the recording label)

table condition
--- conditionid (Unique identifer for a condition in the conditions table)
--- abbr (An abbreviation of the condition's fullname e.g. "M" for Mint condition)
--- fullname (The fullname of the condition e.g."Mint")
--- condesc (A short description of the condition e.g. whatthe product is like for it to be categorised under that condition)


I have created the following query to get the label name and the record condition (rcon) and the (scon):

SELECT *
FROM records
LEFT JOIN labels AS labels ON ( records.label = labels.labelid )
LEFT JOIN condition AS recordcondition ON ( records.rcon = recordcondition.conditionid )
LEFT JOIN condition AS sleevecondition ON ( records.scon = sleevecondition.conditionid )
ORDER BY artistname ASC

However the problem with this query is that as the rcon and scon both reference the condition table (conditionid field) there are two values for "conditionid" (As each record has a separate rating for record condition and the condition of the sleeve).

My real question is that as the record and sleeve condition both come out in the query as "conditionid" is there anyway of renaming these in the query so that they can both be grabbed in a while loop in PHP without having to have separate columns in the condition table for record condition and sleeve condition??

I would ultimately like to be able to grab the corresponding "abbr" from the "conditionid" for both "rcon" and "scon". Can this be done in one query or do I have to have separate queries?

Any help you can give you be appreciated.

Thanks

'blu

When doing multi-table joins like that, it's usually advisable to explicitly list the columns you want to retrieve.

Matt.