Inner or outer join problem

[qadeer_ahmad]

Hi,
I am creating a query dynamically and its working. Most important part is in where condition

select noun_english.noun_id, noun_english.a_te_noun_singular,noun_english.a_te_noun_plural,noun. a_pic_image,noun_spanish.a_te_noun_singular,noun_spanish.a_te_noun_plural, noun_spanish.a_au_audio_singular,noun_spanish.a_au_audio_plural, noun_spanish.a_au_audio_examples,noun_spanish.a_te_examples, noun_spanish.b_te_synonyms_id,noun_spanish.c_au_qadeer2, noun_italian.a_te_noun_singular,noun_italian.a_te_noun_plural, noun_italian.a_au_audio_singular,noun_italian.a_au_audio_plural, noun_italian.a_au_audio_examples,noun_italian.a_te_examples, noun_italian.b_te_synonyms_id,noun_italian.c_au_qadeer2

from noun_english,noun,noun_spanish,noun_italian

where noun_english.noun_id=noun.noun_id and noun.noun_id=noun_spanish.noun_id and noun_spanish.noun_id=noun_italian.noun_id

This query work fine and get records but only those records which are present all the tables as where condition is as noun_id of ist tables is equal to noun_id of second table and so on .
Here i want to show all max records mean if one of the table has 250 records and other not much then the result set show 250 record since field data is null of ohter tables because this record is not present.
Note: I don't know which one has max records.

Thanks

[qadeer_ahmad]

I simplyfy my question i need a query which get record of all tables but relation of the table based on "noun_id" which is present in all tables no duplicate record of "noun_id" needed and also i want to show records of all tables.

Thanks

[laserlight]

This is my analysis:
There is a many-one relationship between nouns (words) and languages. A language has many nouns, but each noun belongs to only one language.

There is a many-many relationship between nouns and meanings of nouns. A word can have many meanings, while a single meaning can be expressed with different words.

This is what I propose for the design of the tables:

Table: languages
language_id
name

Table: nouns
noun_id
language_id
noun

Table: meanings
meaning_id
meaning

Table: nouns_meanings
id
noun_id
meaning_id

Consider two languages, English and Urdu. According to a dictionary I checked, the Urdu words for father are baaba, baap, pidar and waalid, and the words for mother are maañ and waalidah. Suppose that the English word 'father' is also considered to have the meaning 'priest'.

In the languages table:
1, 'English'
2, 'Urdu'

In the nouns table:
1, 1, 'father'
2, 1, 'mother'
3, 1, 'priest'
4, 2, 'baaba'
5, 2, 'baap'
6, 2, 'pidar'
7, 2, 'waalid'
8, 2, 'maañ'
9, 2, 'waalidah'

In the meanings table:
1, 'father'
2, 'mother'
3, 'priest'

In the nouns_meanings table:
1, 1, 1
2, 1, 3
3, 2, 2
4, 3, 3
5, 4, 1
6, 5, 1
7, 6, 1
8, 7, 1
9, 8, 2
10,9, 2

So, if the user wants to add support for Spanish, he/she will add Spanish to the languages table, add the Spanish nouns to the nouns table, then add the relationships in the nouns_meanings table. If a Spanish noun with a meaning not listed is added, then the meanings table will also have a new entry.

[qadeer_ahmad]

Thanks a lot for this help