SQL Question

Enygma

Hey folks,

Trying to get this query working for the last few hours.
In my DB app I've got a MAIN table and a MAIN_PROPERTY table.
They look something like this

MAIN
====
ID
CONTENT
ETC
ETC


MAIN_PROPERTY
============
ID
MAIN_ID
PROP_NAME
PROP_VALUE

The MAIN.ID and MAIN_PROPERTY.MAIN_ID values link up.
I want to be able to find MAINs where PROP_NAME = 'something' AND PROP_VALUE = 'something_value'
AND PROP_NAME = 'something_else' AND PROP_VALUE = 'something_else_value'

etc etc.

I think im in over my head here

Can anyone shed any light on this for me please? Cheers!

MySQL 4.0.1 BTW

seamus

Ok, what I'm thinking is this (but I dunno if it's right).

SELECT * FROM MAIN_PROPERTY WHERE (PROP_NAME = 'something' OR PROP_NAME = 'something_else') AND (PROP_VALUE = 'something_value' OR PROP_VALUE = 'something_else_value')

But I don't know if you can group boolean statments like that

Enygma

No that doesn't work for some reason. I think it's because MAIN_PROPERTY.PROP_NAME can't be two things at once.
That's why if you use an OR in between the two main clauses then it works.

Enygma

Was looking on the MySQL.com website there (the documentation is really good and very well presented)
Found some guy who had posted some similar code.

Here's what I managed to come up with, seems to be working

SELECT M.* FROM MAIN AS M
LEFT JOIN MAIN_PROPERTY mp1 ON mp1.PROP_NAME = 'something' AND mp1.PROP_VALUE = 'something_value'
LEFT JOIN MAIN_PROPERTY mp2 ON mp2.PROP_NAME = 'something_else' AND mp2.PROP_VALUE = 'something_else_value'
.
.
.
.
LEFT JOIN MAIN_PROPERTY mp[n] ......etc
WHERE M.ID = mp1.MAIN_ID

Don't really know (or care at this stage) how efficient it is but it works! I can go out now

Enygma

One small alteration,
for each join you do, you have to add a
AND M.ID = mp[n].MAIN_ID

Just incase someone else has the same problem

bonkey

Thats the right solution, but it can be made a bit neater/more efficient :

You should change the LEFT JOIN to an INNER JOIN in each case, and also move the M.ID=Mp[n].ID comparison into each of the JOIN clauses, rather than applying them at the end in a WHERE clause.

Thusly (for two descriptions only) :

SELECT  M.* 
  FROM  MAIN AS M
  INNER JOIN MAIN_PROPERTY mp1 
    ON  mp1.MAIN_ID = M.ID
    AND mp1.PROP_NAME = 'something' 
    AND mp1.PROP_VALUE = 'something_value'
  INNER JOIN MAIN_PROPERTY mp2 
    ON  mp2.MAIN_ID = M.ID
    AND mp2.PROP_NAME = 'somethingElse' 
    AND mp2.PROP_VALUE = 'somethingElse_value'

jc

Enygma

Thanks very much!