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

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

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

python - PyCharm Type error Message -

-
i'm newbie trying out ml/dbn in python (3.4) under pycharm (comm.ed 4.5). following definition def make_thetas(xmin,xmax,n): xs = np.linespace(xmin,xmax,n) widths = (xs[1:] - xs[:-1])/2.0 thetas = xs[:-1] + widths return thetas throws error "class 'tuple' not define ' sub ', '-' operator cannot used on instance" on - operator on third line (widths = ....) ideas on how code running under pycharm - works alright in interactive python window. thx. to all, after g'g lot have found workaround seems work within pycharm. workaround because (even python newbie) expected python not need explicit typecasting, not when using datatypes imported lib such numpy. def make_thetas(xmin,xmax,n): xs = np.array(np.linspace(xmin,xmax,n)) widths = (xs[1:] - xs[:-1])/2.0 thetas = xs[:-1]+ widths return thetas using docstrings type-hinting such below did not work def make_thetas(xmin,xmax,n): "&q
Read more