Strange SELECT

[mcibor]

Hello all! I have a simple/not so simple mySQL query question.

I have a simple search engine, 3 tables: data, keywords, assign. In data there's all relevant data, in keywords there's a keyword (and some info about it-how many times used, etc), in assign there's data.id and keywords.id.

Querying the db for specific keyword is no problem at all - simple join. However my problem is: I want to show keywords, that are not yet assigned to anything (I can't assign them automatically, because data is just a folder full of files, and I can only do name search).

Here's an example:

data:
id, name, desc
1, PHP, Script language
2, MySQL, Database

keywords:
id, keyword
1, MySQL
2, Database
3, SELECT
4, PHP
5, Script
6, HTML

assign:
id, dataid, keyid
1, 1, 4 #assign PHP -> PHP, etc...
2, 1, 5
3, 2, 1
4, 2, 2

In this query I would like to show from keywords:
'SELECT' and 'HTML'

Then the rest, however I use UNION for that

Maybe it could be done with simple ORDER BY then I won't need the UNION and it would be great for me.

Thanks for any help
Best regards
Michal Cibor

[bsterz]

Unless I misunderstand - it seems that a LEFT JOIN is what you need - have you tried this:

SELECT keywords.keyword, data.name, data.desc, assign.dataid, assign.keyid
FROM (keywords INNER JOIN data ON keywords.id = data.id) LEFT JOIN assign ON data.id = assign.id

[mcibor]

Sadly your solution didn't work.

If I have a keyword id 15 that is assigned to data id 1 and data id 15 isn't assigned to anything then the result is NULL. (I check the assign.key_id)

From all the data I really would like to get only not assigned keywords, so your solution worked fine, however not to the end.

You must remember, that keywords and data aren't correlated in any way.

So the problem still lasts. Please help.

Best regards
Michal Cibor

[mcibor]

I've another question.
I found the answer to the above mentioned problem:

SELECT id, keyword FROM keywords WHERE id NOT IN (
SELECT DISTINCT keyid FROM assign)

This works fine!

Then I wanted to show the rest of keywords, so I used UNION

(SELECT id, keyword FROM keywords WHERE id NOT IN (
SELECT DISTINCT keyid FROM assign))
UNION
(SELECT id, keyword FROM keywords)

This works fine as well

The last (I think) question is
Can I distinguish the keywords from first select and the rest?

(SELECT id, keyword, 0 FROM keywords WHERE id NOT IN (
SELECT DISTINCT keyid FROM assign))
UNION
(SELECT id, keyword, 1 FROM keywords)

This didn't work, because the result was almost all of the keywords returned twice.

Best regards
Michal Cibor

[arran]

Hi Michal,

If i follow what you're saying then:

(SELECT id, keyword, 0 FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign)) UNION (SELECT id, keyword, 1 FROM keywords [b]WHERE id IN (SELECT DISTINCT keyid FROM assign)[/b]);

could be what you're after.

arran.

[mcibor]

Thanks arran for your help! It solved the problem of double rows.

However now I have a little problem with ordering the rows:

I want to order them by keyword, not id, however the example below does't work

(SELECT id, keyword, 0 FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign) ORDER BY keyword) UNION (SELECT id, keyword, 1 FROM keywords WHERE id IN (SELECT DISTINCT keyid FROM assign));

Can anybody tell me where the problem lies?

Thanks
Michal Cibor

PS. I tried ORDER BY keywords.keyword. Didn't work either.

[grandpa]

This is what I would try next.. not saying it will work, mind you ;)

(SELECT id, keyword, 0 FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign) UNION (SELECT id, keyword, 1 FROM keywords WHERE id IN (SELECT DISTINCT keyid FROM assign)) ORDER BY keyword);

[arran]

I agree with grandpa - you need to order the result of the union.

[mcibor]

But won't the records mix together after that? The main reason for the UNION is to keep them one after another.

But no, there's a way for that too. Just need to give 0 and 1 some name and oder by it in the first place:

(SELECT id, keyword, 0 AS state FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign)) UNION (SELECT id, keyword, 1 AS state FROM keywords WHERE id IN (SELECT DISTINCT keyid FROM assign)) ORDER BY state keyword;

Thanks guys!

Michal Cibor

[jd01]

Just for reference, you can change the ORDER BY values, depending on where the statement is:

Will order both sets:
(SELECT id, keyword, 0 AS state FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign))
UNION
(SELECT id, keyword, 1 AS state FROM keywords WHERE id IN (SELECT DISTINCT keyid FROM assign)) ORDER BY state keyword;

Will order the second set:
(SELECT id, keyword, 0 AS state FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign))
UNION
(SELECT id, keyword, 1 AS state FROM keywords WHERE id IN (SELECT DISTINCT keyid FROM assign) ORDER BY state);

Will order the both sets, individually:
(SELECT id, keyword, 0 AS state FROM keywords WHERE id NOT IN ( SELECT DISTINCT keyid FROM assign) ORDER BY state)
UNION
(SELECT id, keyword, 1 AS state FROM keywords WHERE id IN (SELECT DISTINCT keyid FROM assign) ORDER BY state);

* I believe you must use a LIMIT statement for individual ordering EG LIMIT 0,999999999

** Would reference the documentation, but dev.mysql.com seems to be having issues.

Justin