Make the records which are in tag table outputting first

Databases
1 
(sayID) say
1  Germany is a country
2  Berlin is a city in Germany
3  China is in Asia
4  Paris is in France
5  the capital of Italy is Rome
6  Japan is not a continent
7  Japan and China are both in Asia
8  Rome is near to sea
9  Many people go to Rome for tour
10  Tokyo is in Japan
11. Beautiful city, Paris[/code]I have a table named "say" like the above and another table named "tag" like the below.

[code]
(tagID) tag
1  Germany
7  China
9  Rome
6 Japan
10  Japan
[/code]

Let's suppose that I have a key which is "Japan".

[code]SELECT sayID, say
FROM say where say like '%$key%'
ORDER BY sayID[/code]
With the code above I can get the result below.
[code]
(6) Japan is not a continent
(7) Japan and China are both in Asia
(10) Tokyo is in Japan[/code] I like to produce like the below.
[code](6) Japan is not a continent
(10) Tokyo is in Japan
(7) Japan and China are both in Asia
[/code](6) and (10) are come before (7) because (6) and (10) are in tag table.


When the key is "Rome",  I like to produce like the below.  Please, notice "(9) Rome" is in tag table.
[code](9)  Many people go to Rome for tour
(8)  Rome is near to sea
[/code]

When the key is "Germany",  I like to produce like the below.  Please, notice "(1) Germany" is in tag table.
[code](1)  Germany is a country
(2)  Berlin is a city in Germany

When the key is “China” , I like to produce like the below. Please, notice “(7) China” is in tag table.

code Japan and China are both in Asia
(3) China is in Asia
[/code]

When the key is “Paris” , I like to produce like the below. Please notice “no Paris” is in tag table.

code Paris is in France
(11) Beautiful city, Paris
[/code]

2

They’re sorting by the ID.

To sort in another order, you’ll need to have another field which indicates sort values. It can be as simple as a different numeric value, or as complex as a field which is a foreign key to another table that allows you to sort by different categories.

3

SELECT sayid, say FROM say LEFT JOIN tag ON tagid=sayid WHERE tag='$key' OR say like '%$key%' ORDER BY tagid, sayid
I made the code above, but it produces the result below.

(6) Japan is not a continent (7) Japan and China are both in Asia (10) Tokyo is in JapanThe following is my target result when $key is “Japan”.

code Japan is not a continent
(10) Tokyo is in Japan
(7) Japan and China are both in Asia[/code]

4

SELECT sayid, say FROM say LEFT JOIN tag ON tagid=sayid WHERE tag='$key' OR say like '%$key%' ORDER BY tag, sayid I made the code above as another trial for my target result.
The following is the result of if.

code Japan and China are both in Asia
(6) Japan is not a continent
(10) Tokyo is in Japan
[/code]
I like to put (10) second instead of the last because (10) is in tag table but (6) is not in tag table.
How can I put the record which is in tag table coming first and the record which is not in tag table coming later?

The following is my target result.
(6) and (10) are coming first because they are in tag table.
(7) is coming later because it is not in tag table.

code Japan is not a continent
(10) Tokyo is in Japan
(7) Japan and China are both in Asia
[/code]

5

That query looks foobar.

Perhaps it would help if you use qualified or aliased tables in the query? eg.

SELECT tag.tagword, say.sayword 
FROM tag 
LEFT JOIN say ON say.sayid = tag.tagid 
WHERE tag.tagword LIKE '%inputword%' 
ORDER BY say.sayid
6

SELECT tag.tagword, say.sayword FROM tag LEFT JOIN say ON say.sayid = tag.tagid WHERE tag.tagword LIKE '%inputword%' ORDER BY say.sayid As I test Mittineague’s code above, it produces the result below.

codeJapan is not a continent
(10)Tokyo is in Japan[/code]Yes, it produces (6) and (10). I like the order of it. (6) comes 1st, and (10) comes 2nd, but where is (7)?

I like to produce (6), (10), (7) all and with the order.

7

7 is tagged to China, not to Japan, so it will not show up.

8

[quote=“DaveMaxwell, post:7, topic:273431”]
7 is tagged to China, not to Japan, so it will not show up.
[/quote]Yes, 7 is tagged to China.
Because (7) in tag table is not relate to Japan, (7) should not be shown when we concern tag table only.

But there is the inputword “Japan” in (7) of say table, . That is the reason whey (7) should be produced in the result.
However it should come last because there is no relation between the inputword " Japan and (7) in tag table while there is the inputword “Japan” in (6) and (10) of tag table.

9

SQL is essentially stupid - it will only give you what you ask for. If you don’t ask for the correct thing, you’re not going to get the correct answer.

You only got the records which had the Japan tag because that’s what you asked for. To get values if the tag or the sayword has the value AND you want the tags sorted first and the saywords sorted after, you’ll need to do some pre-querying (or sub-querying) and then use the UNION ALL to get the results you need.

SELECT tagword
     , sayword
  FROM (SELECT tag.tagword
             , say.sayword 
          FROM tag 
          LEFT JOIN say ON say.sayid = tag.tagid 
         WHERE tag.tagword LIKE '%keyword%' 
         ORDER BY say.sayid) T
UNION ALL
SELECT tagword
     , sayword
  FROM (SELECT tag.tagword
             , say.sayword 
          FROM tag 
          LEFT JOIN say ON say.sayid = tag.tagid 
         WHERE tag.tagword NOT LIKE '%keyword%' 
           AND say.sayword LIKE '%keyword%' 
         ORDER BY say.sayid) S
1 Like
10

Thank you, Mittineague.
Your code produces the following which is very close what I want…

[code]Japan is not a continent
Tokyo is in Japan
Japan and China are both in Asia

[/code]
I like to add say.id to the result above.

code Japan is not a continent
(10) Tokyo is in Japan
(7) Japan and China are both in Asia
[/code]in order to get the result above, I add “say.id” like the follow in both SELECT divided by UNION

[quote=“DaveMaxwell, post:9, topic:273431”]
SELECT say.id, tagword
, sayword
FROM (SELECT tag.tagword
, say.sayword
FROM tag
LEFT JOIN say ON say.sayid = tag.tagid
WHERE tag.tagword LIKE ‘%keyword%’
ORDER BY say.sayid) T
UNION ALL
SELECT tagword
, sayword
FROM (SELECT say.id, tag.tagword
, say.sayword
FROM tag
LEFT JOIN say ON say.sayid = tag.tagid
WHERE tag.tagword NOT LIKE ‘%keyword%’
AND say.sayword LIKE ‘%keyword%’
ORDER BY say.sayid) S
[/quote]But as I add “say.id”, it produces an error saying mysql_fetch_array(): supplied argument is not a valid MySQL.

11

I’m not Mitt :wink:

However, to answer your question, you didn’t add say.id correctly…you need it in the subqueries AND in the outside unioned queries.

SELECT say.id
     , tagword
     , sayword
  FROM (SELECT say.id
             , tag.tagword
             , say.sayword 
          FROM tag 
          LEFT JOIN say ON say.sayid = tag.tagid 
         WHERE tag.tagword LIKE '%keyword%' 
         ORDER BY say.sayid) T
UNION ALL
SELECT say.id
     , tagword
     , sayword
  FROM (SELECT say.id
             , tag.tagword
             , say.sayword 
          FROM tag 
          LEFT JOIN say ON say.sayid = tag.tagid 
         WHERE tag.tagword NOT LIKE '%keyword%' 
               AND say.sayword LIKE '%keyword%' 
         ORDER BY say.sayid) S
12

"SELECT tagid, sayid, tag , say FROM (SELECT tagid, sayid, tag.tag , say.say FROM tag LEFT JOIN say ON say.sayid = tag.tagid WHERE tag.tag LIKE '%$key%' ORDER BY say.sayid) T UNION ALL SELECT tagid, sayid, tag , say FROM (SELECT tagid, sayid, tag.tag , say.say FROM tag LEFT JOIN say ON say.sayid = tag.tagid WHERE tag.tag NOT LIKE '%$key%' AND say.say LIKE '%$key%' ORDER BY say.sayidWith the code above, it produces the resutl below when key is Japan.

code Japan is not a continent
(10) Tokyo is in Japan
(7) China / Japan and China are both in Asia[/code]The result above is the my target result.
However, When the key is “Germany”, it produces the result below.

code Germany is a country[/code]The result below is my target result.

code Germany is a country
(2) Berlin is a city in Germany[/code].

13

That’s because you don’t have a 1:1 ratio between tags and sayings. Reverse the JOIN on the second query. Honestly you could reverse the joins on both queries and get a little better performance.because the first query can be a INNER JOIN which performs better

SELECT say.id
     , tagword
     , sayword
  FROM (SELECT say.id
             , tag.tagword
             , say.sayword 
          FROM say 
         INNER JOIN say ON say.sayid = tag.tagid 
         WHERE tag.tagword LIKE '%keyword%' 
         ORDER BY say.sayid) T
UNION ALL
SELECT say.id
     , tagword
     , sayword
  FROM (SELECT say.id
             , tag.tagword
             , say.sayword 
          FROM say
          LEFT JOIN tag ON say.sayid = tag.tagid 
         WHERE tag.tagword NOT LIKE '%keyword%' 
               AND say.sayword LIKE '%keyword%' 
         ORDER BY say.sayid) S
14

[quote=“DaveMaxwell, post:13, topic:273431”]
SELECT say.id
, tagword
, sayword
FROM (SELECT say.id
, tag.tagword
, say.sayword
FROM say
INNER JOIN say ON say.sayid = tag.tagid
WHERE tag.tagword LIKE ‘%keyword%’
ORDER BY say.sayid) T
UNION ALL
SELECT say.id
, tagword
, sayword
FROM (SELECT say.id
, tag.tagword
, say.sayword
FROM say
LEFT JOIN tag ON say.sayid = tag.tagid
WHERE tag.tagword NOT LIKE ‘%keyword%’
AND say.sayword LIKE ‘%keyword%’
ORDER BY say.sayid) S
[/quote]The code above produces an ERROR.

I think the error is coming from the code below.

So I tried to change the code above like the following.

FROM say INNER JOIN tag ON say.sayid = tag.tagid But the trial code above produces the following result.

code Germany is a country[/code] instead of my target result below

code Germany is a country
(2) Berlin is a city in Germany[/code]

15

You are correct. That was a copy/paste error from me trying to squeeze in that post when I didn’t have the time to double check it.

Hmmm…Try this (it worked against my local “copy” of these tabkes)

Change the WHERE clause in the second sub query from

WHERE tag.tagword NOT LIKE '%keyword%' 
  AND say.sayword LIKE '%keyword%'

to

WHERE (tag.tagword IS NULL OR tag.tagword NOT LIKE '%keyword%')
  AND say.sayword LIKE '%keyword%'

That’s an odd quirk I didn’t think would occur but I guess it makes sense. Because the join resulted in no match, there was nothing to compare to with in the LIKE statement. The NULL is a complete lack of value, not a “filled in” value from the DBMS, which is what I assumed it would be.

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.