• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL - Return a list of members only if they are not in another list

 
Ranch Hand
Posts: 164
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I  am trying to create a list of members only if they are not in another list. When the other list has at least one member then the query works (member not in the other list are returned). However, when the other list contains no members (returns NULL) then no values are returned.

 
Sheriff
Posts: 5555
326
IntelliJ IDE Python Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There's a good chance your query would be much simpler if you were to use the EXCEPT Operator.
 
Saloon Keeper
Posts: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Another thing that would probably make the query a lot simpler (and possibly more efficient) - if the DBA likes you - is to crunch all that sub-SELECT stuff down into a View and work with it instead. Bonus is that if you later have to change the field set/selection criteria, you only have to change it in one place and not risk forgetting to change the other.
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I took the String that you created and modified it back to an SQL statement (below).  I may be wrong, but I think you go off the rails at the AND that I marked with a comment.  It seems that it could be controlled by the second WHERE clause, but the parentheses do not match.
 
Tim Holloway
Saloon Keeper
Posts: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The more I look at this, the more reasons I come up with for this:


I honestly cannot follow the complexity of the original statement because it's obscured by its own repetitive complexity. I think that by defining a view for the sub-queries, you can reduce it to something similar to this:


And although I'm sure I'm missing something, this is as you can plainly see a lot easier shape to work with. And, again, probably can be better optimized by the SQL compiler and the DBA as a side benefit.

The original SQL loses the forest for the trees, and the use of those "AND" clauses under the outermost WHERE doesn't seem to make sense as far as I can read them.
 
Glyndwr Bartlett
Ranch Hand
Posts: 164
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I really appreciate all the help you are providing. On another site I am getting sledged. Tim H you are totaly correct that this is very complexed so I have simplified it below. Knute I have tried your change with no difference. Tim C I am afraid the MySQL does not support "EXCEPT". Simplified:

This works:

   

This does not work:

   

Initially I only had the third "SELECT". I then added the second "SELECT" to check for "NULL" being returned by the third "SELECT". However, that did not correct the issue.

So "SELECT":
One returns the required values (all members not attached to an Account - not in the third "SELECT")
Two checks for "NULL" (i.e., no members currently attached to an Account - so return all members)
Three returns all members currently attached to the Account and therefore should be excluded from the list returned by the first "SELECT")

I am not a programmer, learning as I go and doing this for my Scouts.

Kind regards,

Glyn

 
Tim Holloway
Saloon Keeper
Posts: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually, if you could describe what data you want the query to return in English (sorry, no Welsh!) then we might be able to construct what you want using much simpler terms.

It's a common problem - even for the most technically advanced of us - to think up a solution and get stuck trying to implement it, and sometimes it's better to step back and recall what the actual problem is. Especially when asking for help, since we may know other (and as I said, often simpler) ways to solve the problem.

Incidentally, EXCEPT joined the "MySQL" family starting with MariaDB version 10.3.0. Since MariaDB is a fork from MySQL in protest to Oracle's taking over MySQL itself, if you are actually using MySQL instead of MariaDB, it may or may not be available on that fork.

Regardless, I've lots of practice with the "NOT IN" construct, so no problem there.
 
Glyndwr Bartlett
Ranch Hand
Posts: 164
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim H,

I posted a detailed reply and for some reason it is not here. I obviously failed to hit submit. Anyway, after completing the reply I was thinking (yes it did hurt) and did some further investigation and found "IFNULL". After some experimenting it worked. You deserve the credit for jogging this thought. Thank you for teaching me to fish.

For those interested the answer is to return 0 if null:



Kind regards,

Glyn
 
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So long as 0 is not a valid cd_id...
 
Glyndwr Bartlett
Ranch Hand
Posts: 164
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Dave,

I am hearing you brother. No 0 is not valid. :-)

Kind regards,

Glyn
 
reply
    Bookmark Topic Watch Topic
  • New Topic