how to get this resultset...

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! :)
ibizaTry the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvansql