SQL SELECT help needed

ahmetaa

Hello i cannot create an sql sentence for a rather simple task, i would appreciate any help. (PHP-mysql project)

Lets say i have two tables TableA and TableB
and TableC is used for relation of those two tables.

Tables and columns are like this:

TableA

id name

1 jack
2 tom
3 ali

TableB

id language

1 english
2 french
3 spanish
4 turkish

And suppose that
jack knows english and french
tom knows english and spanish
ali knows turkish

TableC forms like this:

TableC

idA idB

1 1
1 2
2 1
2 3
3 4

Ok, now, i need an SQL select sentence that brings the languages a person does NOT know.

for jack, (TableA, id=1)
result will be:
3 spanish
4 turkish

for ali, result will be
1 english
2 french
3 spanish

Thanks for any help.

Ahmet

steadyguy

Are the number of languages that can be entered limited? If they are, instead of having 3 tables, you could have only one. For example:

CREATE TABLE proficiencies (
id MEDIUMINT(5) unsigned NOT NULL auto_increment,
name VARCHAR(32) NOT NULL default '',
langs SET('english', 'spanish', 'portuguese', 'french', 'japanese', 'chinese') default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

When you enter data, do it something like:
INSERT INTO proficiencies
VALUES('', 'steadyguy', 'english, japanese, portuguese, chinese, french');

Since you know the set of languages a user can know, you can also SELECT data with something like:
SELECT id, name
FROM proficiencies
WHERE lang='english';

To get all people who speak English, or
SELECT id, name
FROM proficiencies
WHERE lang!='spanish';
To get all people who don't speak Spanish.

However ... you might want to let people enter an extra language or two that is not on the list (you can't predict them all , and SET columns can only take 64 values.) So, you could add an 'other' column (or two, or three) ... then search accordingly.

Personally, I know it's not a direct answer to your question, but ... I think this is an easier approach. My 2cents.

ahmetaa

i greatly appreciate your time and help, however, as you said this is not answering my question.

this was only an example, in real case the table rows may be much more than 100 or 1000.
And in your example you give only one table, what i need is exactly in the question, there will be three tables, for other reasons.
what i need is more general solution for this particular problem.

thanks anyway.

tekky

hmmm still playing, I'll figure it out after dinner hopefully 😃

steadyguy

Yes, what I gave you was an example, but it can be extrapolated into real-world usable SQL with very little effort.

BTW, what are the reasons for a three table setup? If you're worried about speed - then a three table approach will make the query slower ... With a single table, it doesn't matter if there are 1,000,000 records - the query will still be quite fast, whereas the multi-table query will quickly become noticibly slower.

If you don't like the SET idea ... there are other options ...

ahmetaa

No no.. i have to use the three table mathod because it is a many-to-many relation. i do not have (almost)any speed concerns.
i think it is possible with a sub query or a join but still trying to find..

ahmetaa

OK. Here is the answer, i thank K.Bogac Bokeer from Turkiye.

By using left Join:

SELECT
B.language
FROM
B LEFT JOIN C ON
C.idb = B.id AND C.ida = 1 <--- person id
WHERE
C.idb is null

By using sub query:

SELECT
B.language
FROM
B
WHERE
id NOT IN (
SELECT
idB
FROM
A INNER JOIN
C ON C.ida = A.id
WHERE
A.id = 1 <--- person id
)

And probably lef join method is much faster.