0

I need to realize logic "All, except in list" with single query to show a widget on all routes, except listed. I have next query:

SELECT *
FROM widgets w0_ 
LEFT JOIN widget_route w1_ 
    ON w0_.id = w1_.widget_id 
LEFT JOIN routes r2_ 
    ON w1_.route_id = r2_.id 
WHERE r2_.id IS NULL OR w1_.id = 3

Where 3 is an ID of current route, that I have dynamically. But I need to except widget from routes, listed in widget_route table

There is table structure:

widgets: id, name, published

widget_route: id, widget_id, route_id

routes: id, name, path

Can anybody help me?

Victor Bocharsky
  • 11,930
  • 13
  • 58
  • 91
  • "WHERE r2_.id IS NULL OR w1_.id = 3" - I'm not saying it's wrong, but I always struggle to understand what this kind of statement means in the context of an OUTER JOIN. :-( Is there another way of expressing this sentiment? Anyway, if you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Aug 04 '14 at 08:53
  • @Strawberry no. It means "where no matching entry in r2 is found or w1_.id = 3" – TheConstructor Aug 04 '14 at 08:54
  • @Strawberry Ok, I try to replicate problem with `sqlfiddle` now – Victor Bocharsky Aug 04 '14 at 08:57
  • How do you want it excluded from routes? Do you want to select all other routes or do you also get a route id? – TheConstructor Aug 04 '14 at 09:00
  • @TheConstructor I need to get all widgets, but not that listed in `widget_route` table. It like [Joomla module assignment](http://joomla32.cloudaccess.net/administrator/index.php?option=com_modules&view=module&layout=edit) "On all pages except those selected" in `menu assignment` tab when create new module (login and pass "demo") – Victor Bocharsky Aug 04 '14 at 09:03

1 Answers1

1

This is how you select all widgets without any entry in widget_route

by inner select:

SELECT
  *
FROM widgets w
WHERE w.id NOT IN (SELECT
                     wr.widget_id
                   FROM widget_route wr);

by left join:

SELECT
  *
FROM widgets w
  LEFT JOIN widget_route wr ON wr.widget_id = w.id
WHERE wr.id IS NULL;

This is how you select all widgets not linked to your route with id 3 by widget_route:

by inner select:

SELECT
  *
FROM widgets w
WHERE w.id NOT IN (SELECT
                     wr.widget_id
                   FROM widget_route wr
                   WHERE wr.route_id = 3);

by left join:

SELECT
  *
FROM widgets w
  LEFT JOIN widget_route wr ON wr.widget_id = w.id AND wr.route_id = 3
WHERE wr.id IS NULL;
TheConstructor
  • 4,285
  • 1
  • 31
  • 52
  • I almost always do `LEFT JOIN`s for this sort of thing. Personal preference – Dave Aug 04 '14 at 09:13
  • @Dave yeah, I will normally also do `LEFT JOIN`s as they are [often faster, but probably less readable](http://stackoverflow.com/a/2577224/1266906). – TheConstructor Aug 04 '14 at 09:16
  • @TheConstructor, thanks, I tested last query in your example, and in output I has 2 widgets, but first widget in `widget_route` list and must be excluded http://sqlfiddle.com/#!2/ecf91/8 – Victor Bocharsky Aug 04 '14 at 09:29
  • @Victor your widget_route table contains only links to the routes 6, 7 and 1. My examples selects all widgets not linked to route 3 - which are all widgets. – TheConstructor Aug 04 '14 at 09:33
  • @TheConstructor Yes, you're right, I looked at `widget_route.id`, not at `route_id`, Thanks! – Victor Bocharsky Aug 04 '14 at 09:38