|
|
Posted by ibiza on 09/24/06 01:46
Hi,
I have 3 tables as follow :
Kanji :
kanji_id
....
References :
ref_id
....
KanjiRefs
kref_idkanji
kref_idref
kref_value
....
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks! :)
ibiza
Navigation:
[Reply to this message]
|