mysql - LEFT JOIN shows different results from RIGHT JOIN - when joining table on itself... why? -

-

i have single taxonomies table parent->child hierarchical data. thought queries post below should return identical result because i'm joining same table on - i'm getting different results. why?

here's left join query , it's result:

select a.id cat_id, b.id subcat_id, a.name cat_name, b.name sub_cat_name, concat_ws(' / ', a.name, b.name) full_name  taxonomies left join taxonomies b  on a.id = b.parent_taxonomy_id  (a.name 'se%' or b.name 'se%') order full_name desc;  +--------+-----------+----------+--------------+--------------------+ | cat_id | subcat_id | cat_name | sub_cat_name | full_name          | +--------+-----------+----------+--------------+--------------------+ |     84 |        85 | season   | winter       | season / winter    | |     84 |        91 | season   | summer       | season / summer    | |     84 |        90 | season   | spring       | season / spring    | |     84 |       128 | season   | fall         | season / fall      | |     84 |       129 | season   | christmas    | season / christmas | +--------+-----------+----------+--------------+--------------------+ 5 rows in set (0.00 sec)

```

here's right join query , it's result:

select a.id cat_id, b.id subcat_id, a.name cat_name, b.name sub_cat_name, concat_ws(' / ', a.name, b.name) full_name  taxonomies right join taxonomies b  on a.id = b.parent_taxonomy_id  (a.name 'se%' or b.name 'se%') order full_name desc;  +--------+-----------+----------+--------------+--------------------+ | cat_id | subcat_id | cat_name | sub_cat_name | full_name          | +--------+-----------+----------+--------------+--------------------+ |     84 |        85 | season   | winter       | season / winter    | |     84 |        91 | season   | summer       | season / summer    | |     84 |        90 | season   | spring       | season / spring    | |     84 |       128 | season   | fall         | season / fall      | |     84 |       129 | season   | christmas    | season / christmas | |   null |        84 | null     | season       | season             | +--------+-----------+----------+--------------+--------------------+ 6 rows in set (0.00 sec)

here's data in taxonomies table:

mysql> select * taxonomies; +-----+-----------------------+-----------------------+--------+----------------+--------------------+---------+---------------------+---------------------+ | id  | name                  | machine_name          | app_id | is_subcategory | parent_taxonomy_id | user_id | created_at          | updated_at          | +-----+-----------------------+-----------------------+--------+----------------+--------------------+---------+---------------------+---------------------+ |  84 | season                | season                | mw     |              0 |               null |   94711 | 2015-04-10 12:00:00 | 2015-04-10 12:00:00 | |  85 | winter                | winter                | mw     |              1 |                 84 |   94711 | 2015-04-10 12:00:00 | 2015-04-10 12:00:00 | |  90 | spring                | spring                | mw     |              1 |                 84 |   94711 | 2015-04-10 12:00:00 | 2015-04-10 12:00:00 | |  91 | summer                | summer                | mw     |              1 |                 84 |   94711 | 2015-04-10 12:00:00 | 2015-04-10 12:00:00 | | 128 | fall                  | fall                  | mw     |              1 |                 84 |   94711 | 2015-07-09 13:18:03 | 2015-07-09 13:18:03 | | 129 | christmas             | christmas             | mw     |              1 |                 84 |   94711 | 2015-07-09 13:18:11 | 2015-07-09 13:18:11 | | 130 | content type          | content-type          | mw     |              0 |               null |   94711 | 2015-07-09 13:18:47 | 2015-07-09 13:18:47 | | 131 | trend watch           | trend-watch           | mw     |              1 |                130 |   94711 | 2015-07-09 13:19:10 | 2015-07-09 13:19:10 | | 132 | charm unit            | charm-unit            | mw     |              1 |                130 |   94711 | 2015-07-09 13:19:17 | 2015-07-09 13:19:17 | | 133 | infographic           | infographic           | mw     |              1 |                130 |   94711 | 2015-07-09 13:19:23 | 2015-07-09 13:19:23 | | 134 | word art              | word-art              | mw     |              1 |                130 |   94711 | 2015-07-09 13:19:29 | 2015-07-09 13:19:29 | | 135 | storify               | storify               | mw     |              1 |                130 |   94711 | 2015-07-09 13:19:35 | 2015-07-09 13:19:35 | | 136 | content label         | content-label         | mw     |              0 |               null |   94711 | 2015-07-09 13:19:59 | 2015-07-09 13:19:59 | | 137 | usage                 | usage                 | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:04 | 2015-07-09 13:20:04 | | 138 | word history          | word-history          | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:10 | 2015-07-09 13:20:10 | | 139 | spelling bee          | spelling-bee          | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:16 | 2015-07-09 13:20:16 | | 140 | obscure words         | obscure-words         | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:22 | 2015-07-09 13:20:22 | | 141 | words we're watching  | words-were-watching   | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:29 | 2015-07-09 13:20:29 | | 142 | word lists            | word-lists            | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:34 | 2015-07-09 13:20:34 | | 143 | language acquisition  | language-acquisition  | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:41 | 2015-07-09 13:20:41 | | 144 | trending              | trending              | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:45 | 2015-07-09 13:20:45 | | 145 | best of               | best-of               | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:50 | 2015-07-09 13:20:50 | | 146 | pop culture           | pop-culture           | mw     |              1 |                136 |   94711 | 2015-07-09 13:20:55 | 2015-07-09 13:20:55 | | 147 | politics              | politics              | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:01 | 2015-07-09 13:21:01 | | 148 | science               | science               | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:06 | 2015-07-09 13:21:06 | | 149 | grammar               | grammar               | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:10 | 2015-07-09 13:21:10 | | 150 | travel                | travel                | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:14 | 2015-07-09 13:21:14 | | 151 | history               | history               | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:20 | 2015-07-09 13:21:20 | | 152 | sports                | sports                | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:25 | 2015-07-09 13:21:25 | | 153 | lexicography          | lexicography          | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:35 | 2015-07-09 13:21:35 | | 154 | quotable quotes       | quotable-quotes       | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:48 | 2015-07-09 13:21:48 | | 155 | book excerpts         | book-excerpts         | mw     |              1 |                136 |   94711 | 2015-07-09 13:21:54 | 2015-07-09 13:21:54 | | 156 | religion              | religion              | mw     |              1 |                136 |   94711 | 2015-07-09 13:22:04 | 2015-07-09 13:22:04 | | 157 | lifestyle             | lifestyle             | mw     |              1 |                136 |   94711 | 2015-07-09 13:22:10 | 2015-07-09 13:22:10 | | 158 | literature            | literature            | mw     |              1 |                136 |   94711 | 2015-07-09 13:22:15 | 2015-07-09 13:22:15 | | 159 | online culture        | online-culture        | mw     |              1 |                136 |   94711 | 2015-07-09 13:22:21 | 2015-07-09 13:22:21 | | 160 | topic                 | topic                 | mw     |              0 |               null |   94711 | 2015-07-09 13:22:35 | 2015-07-09 13:22:35 | | 161 | arts & entertainment  | arts--entertainment   | mw     |              1 |                160 |   94711 | 2015-07-09 13:22:43 | 2015-07-09 13:22:43 | | 162 | science & technology  | science--technology   | mw     |              1 |                160 |   94711 | 2015-07-09 13:25:46 | 2015-07-09 13:25:46 | | 163 | home & garden         | home--garden          | mw     |              1 |                160 |   94711 | 2015-07-09 13:25:52 | 2015-07-09 13:25:52 | | 164 | business              | business              | mw     |              1 |                160 |   94711 | 2015-07-09 13:25:57 | 2015-07-09 13:25:57 | | 165 | education & research  | education--research   | mw     |              1 |                160 |   94711 | 2015-07-09 13:26:04 | 2015-07-09 13:26:04 | | 166 | sports & recreation   | sports--recreation    | mw     |              1 |                160 |   94711 | 2015-07-09 13:26:11 | 2015-07-09 13:26:11 | | 167 | health & fitness      | health--fitness       | mw     |              1 |                160 |   94711 | 2015-07-09 13:26:17 | 2015-07-09 13:26:17 | | 168 | history & literature  | history--literature   | mw     |              1 |                160 |   94711 | 2015-07-09 13:26:26 | 2015-07-09 13:26:26 | | 169 | medical               | medical               | mw     |              1 |                160 |   94711 | 2015-07-09 13:26:31 | 2015-07-09 13:26:31 | | 170 | government            | government            | mw     |              1 |                160 |   94711 | 2015-07-09 13:26:36 | 2015-07-09 13:26:36 | | 171 | word of year      | word-of-the-year      | mw     |              0 |               null |   94711 | 2015-07-09 13:26:45 | 2015-07-09 13:26:45 | | 172 | word of year 2013 | word-of-the-year-2013 | mw     |              1 |                171 |   94711 | 2015-07-09 13:26:50 | 2015-07-09 13:26:50 | | 173 | word of year 2014 | word-of-the-year-2014 | mw     |              1 |                171 |   94711 | 2015-07-09 13:26:57 | 2015-07-09 13:26:57 | | 174 | word of year 2015 | word-of-the-year-2015 | mw     |              1 |                171 |   94711 | 2015-07-09 13:27:03 | 2015-07-09 13:27:03 | | 176 | travel                | travel                | mw     |              1 |                160 |   94711 | 2015-07-10 13:31:05 | 2015-07-10 13:31:05 | | 177 | test                  | test                  | mw     |              0 |                  0 |   94706 | 2015-07-16 20:03:19 | 2015-07-16 20:03:19 | +-----+-----------------------+-----------------------+--------+----------------+--------------------+---------+---------------------+---------------------+ 53 rows in set (0.00 sec)

^ if join table on - why in world different results?

to expand on @matrichardson said above; basically

  • the first query getting nodes parents if have them
  • the second getting nodes children if have them

Comments

Post a Comment

Popular posts from this blog

c# - Better 64-bit byte array hash -

-
i need hash algorithm produces 64-bit hash code ( long ) fewer collisions string.gethashcode() , fast (no expensive calls cryptographic functions). here's implementation of fnv still shows 3% of collisions after testing 2 million random strings. need number way lower. void main() { const string chars = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz!#@$%^&*()_+}{\":?><,./;'[]0123456789\\"; const int n = 2000000; var random = new random(); var hashes = new hashset<long>(); int collisions = 0; for(int = 0; < n; i++) { var len = random.next(chars.length); var str = new char[len]; (int j = 0; j < len; j++) { str[j] = chars[random.next(chars.length)]; } var s = new string(str); if(!hashes.add(get64bithash( s ))) collisions++; } console.writeline("collision percentage after " + n + " random strings: " + ((doubl
Read more

webrtc - Which ICE candidate am I using and why? -

-
[questions in bold below] i have setup kurento media server 5.1.3 in datacenter behind firewall running os ubuntu 14.04. has 2 network cards: 222.222.222.222 (eth0 - private ip) 111.111.111.111 (eth1 - public ip) attached below sdp (setremotedescription) when browser connected kurento media server type: answer, sdp: v=0 o=- 5487318114793304426 0 in ip4 0.0.0.0 s=kurento media server c=in ip4 0.0.0.0 t=0 0 a=group:bundle audio video m=audio 59068 rtp/savpf 111 0 c=in ip4 111.111.111.111 a=rtpmap:111 opus/48000/2 a=rtpmap:0 pcmu/8000 a=sendrecv a=rtcp:59068 in ip4 111.111.111.111 a=rtcp-mux a=ssrc:669011897 cname:user39019747@host-6e83e4c2 a=extmap:3 http://www.webrtc.org/experiments/rtp-hdrext/abs-send-time a=mid:audio b=as:20 a=ice-ufrag:ymdk a=ice-pwd:lylifk5ueqzpwm91ddj37e a=fingerprint:sha-256 ff:0f:81:8c:41:4e:b4:b6:c6:d8:36:f3:d6:5f:09:fd:5f:af:13:b3:9d:fc:12:66:ac:f3:56:d6:5b:0a:73:5d a=candidate:1 1 udp 2013266431 111.111.111.111 55239 typ host a=candidate:2 1 udp
Read more

php - Zend Framework / Skeleton-Application / Composer install issue -

-
i trying create zend framework application using skeleton in netbeans. when run composer install, following error message: "c:\php\php.exe" "c:\composer\composer.phar" "--ansi" "--no-interaction" "update" "--dev" using deprecated option "dev". dev packages installed default now. loading composer repositories package information updating dependencies (including require-dev) requirements not resolved installable set of packages. problem 1 - package requires php >=5.5 php version (5.4.42) not satisfy requirement. problem 2 - installation request zendframework/zendframework 2.5.1 -> satisfiable zendframework/zendframework[2.5.1]. - zendframework/zendframework 2.5.1 requires php >=5.5 -> php version (5.4.42) not satisfy requirement. done. apparently, zend framework only needs php 5.3+ contradicts above. need use php 5.4. my composer.json following: { "name": "
Read more