Need HELP on complex SQL query

zizicutter

Hi,

I have difficult query that i did not resolved yet after long long night withour sleeping ... Can someone out there who can help me on this purpose ??

My developpement use MySQL, with 2 linked tables. DOCUMENT and DOCFIELDS.
DOCUMENT simply hold a ID, TITLE and DATE. While DOCFIELD keep teh ID foreign key of a DOCUMENT, a KEY_NAME and KEY_VALUE.

So a DOCUMENT contains 1->N DOCFIELD.
I want to do complex query with something like this :
retriev document with field 'category' must be 'sport' and field 'subcat' must be 'soccer'. And from this result get the ID from document that field 'content' that contains word 'zidane'.

Can uu help me finding a solution ?

Thx

paulsbooker

select * from documents,docfieds
where document.id=docfield.id
and id='some number'

paul

zizicutter

Thx Paul for this reply, but it's not what i wanted to to ... this query is so simple so I've already implement it.
It's quite more complex ...

gyoung

select d.id, key_name, key_value
from
(document d
inner join docfields f on d.id = f.id)
where key_name = 'soccer'
and key_value = 'zidane'

If this isn't what you want, post your table definitions and a sample of the output you want.

slug58

try something like this:

select * from document d1, docfield d2 where d1.id = d2.id and d1.category = 'sport' and d1.subcat = 'soccer' and d1.content = '%zidane%'

but like gary said you need to give more info about your tables and expected results.