Query Question

Anon

I have a query question.
let's say i have a table that looks like this.

fruit characteristics
Apple red
Apple round
Apple Yellow
Banana Yellow
Banana Long
Banana Green

Now i'm trying to develop a query in which if i want to put characteristics as yellow round, apple will come up.
I tried this
SELECT * FROM table WHERE characteristics='red' AND characteristics='round'

that doesn't seem to do it.. if i replace AND with OR it will give both Apple and Banana. Any suggestions?

spider_man

Wait, haven't you ever heard of relational algebra?

Try this:

Table fruits:
fruit varchar(x)
shape char(1) fk shape.id
color1 char(1) fk color.id
color1 char(1) fk color.id

Table shape:
id int pk
shape varchar(x)

Table color:
id int pk
color varchar(x)

So;

apple 1 2 1
banana 2 3 5

These are bogus values, but you get the idea, don't you?

losang

Ryan,

 Under articles/database there is a link that mentions database normalization.  This is helpful for learning good database designs.

Matt

Anon

Neither of the previous posts really addressed the question, which is how to do it. I guess philosophy is more important than practicality.

Try this:

select distinct fruit from fruits where ch='yellow' intersect select distinct fruit from fruits where ch='round'

If you have a broken database that doesn't do intersect, do both selects separately, and write the intersect in code.

Alternative:

select distinct a.fruit from fruits a, fruits b where a.ch='round' and b.ch='yellow' and a.fruit=b.fruit

lucidfx

try OR since characterstics is one column you can't have a single item be both red and round.
This should work if your other query wasn't showing anything.
Eventually you'll want to learn about relations, but its a little work to learn on your own.