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:

Quote:

Originally Posted by

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
>
Razvan