[thelist] SQL question

[Kevin Sedgley]

OK, I think I have a solution:

SELECT categories.path from categories AS cat1, categories WHERE
cat1.category = "cheese" AND categories.category = "stilton"

using "AS" as an SQL alias seems to remove the column name ambiguity.

But if anyone has a better solution so I don't have to create aliases every
time I want to add another 'where' condition, send it!

Thanks,

Kevin

On 5/1/06, Kevin Sedgley <kevin at topophilia.co.uk> wrote:
>
> Hello list,
>
> This is probably blindingly simple but my brain isn't working on this
> lovely sunny bank holiday.
>
> I have a table with the following data:
>
> -----------------------
> | category | path     |
> -----------------------
> | cheese   | /cheese  |
> | cheddar  | /cheddar |
> | stilton  | /stilton |
> | cheese   | /cheddar |
> | cheese   | /stilton |
> -----------------------
>
> (both category and path are primary keys)
>
> If I wanted to search for a path where category is "stilton" and for the
> same path the category "cheese" (so the result would be /stilton) is this
> possible just using SQL? I'm using MySQL so I can use proprietary commands
> if need be.
>
> I was thinking something like SELECT * FROM categories, categories WHERE
> category = "cheese" AND category = "stilton" (so combining the same table
> twice) but this isn't allowed.
>
> Thanks for your time,
>
> Kevin
>
> --
> Topophilia Web Services
> The TechnoCentre
> Coventry University Technology Park
> COVENTRY CV1 2TT
>
> phone: 02476 100011
> fax: 0871 661 6815
> http://topophilia.co.uk
>



--
Topophilia Web Services
The TechnoCentre
Coventry University Technology Park
COVENTRY CV1 2TT

phone: 02476 100011
fax: 0871 661 6815
http://topophilia.co.uk