76176

MySQL Query Select where id does not exist in the JOIN table

Question:

I wanna know a single MySQL query for solving this problem:

I have a table that consists of member ids and location ids..

_________________________________________________________ | | | | | ID | Member Id | Location | |______|________________________|________________________| | | | | | 1 | 2371 | 7 | | | | | | 2 | 5123 | 10 | | | | | | 3 | 2371 | 9 | | | | | | 4 | 5123 | 9 | | | | | | 5 | 565 | 9 | | | | | | 6 | 2371 | 5 | | | | | | 7 | 5123 | 6 | | | | | |______|________________________|________________________|

Another table consists of all the locations data:

__________________________________________________ | | | | Location Id | Location Name | |________________________|________________________| | | | | 1 | ABC | | | | | 2 | BCD | | | | | 3 | CDE | | | | | 4 | DEF | | | | | 5 | EFG | | | | | 6 | GHI | | | | | 7 | HIJ | | | | | 8 | IJK | | | | | 9 | JKL | |________________________|________________________|

I wanna find all location ids from the table where member id 2371 is not present. How can I find this in single query? I know I can do it if I break the query into two pieces.. Like array of all locations.. and array of all locations where member id exist. Then using !in_array function of PHP

Thanks for your time and help in advance.

Answer1:

You can find all locations that member 2371 is in by doing:

SELECT location FROM memberlocation WHERE memberid = 2371

You can find all other locations (the ones you want) by:

SELECT * FROM location WHERE locationid NOT IN ( SELECT location FROM memberlocation WHERE memberid = 2371)

Answer2:

You could use the following join query to filter the results by MemberID:

SELECT l.LocationName FROM MemberLocations AS ml INNER JOIN Locations AS l ON ml.Location = l.LocationID WHERE ml.MemberID != 2371

Recommend

  • ASP.NET MVC Model Binding a Collection With a Prefix
  • Why is Infinity-Infinity NaN? [duplicate]
  • Simple CircleCI 2.0 configuration fails for global NPM package installation
  • How to get rid of event firing boiler plate code?
  • Isabelle: Why do I get completely different results when running try versus sledgehammer
  • ActiveRecord oracle_enhanced adapter can't load ruby-oci8 library
  • originX and originY are not set to center by default in 1.3.9 [closed]
  • Can I have a composite shape as a resource in WinRT XAML?
  • Removing spaces after and before commas (not between words) using command line
  • Entity Framework, how to manually produce inner joins with LinQ to entitites
  • Deploy WAR file in Tomcat, Issue after Deployment
  • How to make html video autoplay on phones and tablets?
  • How Can I access WCF services using a Web Reference?
  • Oracle + Oci8 + php5.6 + Ubuntu 16.04
  • How do I use RestSharp to POST a login and password to an API?
  • Xcode. Image resources added to a test target are not copied into the tests bundle
  • Android How to call a method multiple times with a delay between them
  • How can I filter out text twice in Powershell?
  • Setup SignalR and Service Bus inside a Azure Service Fabric service
  • Querying a collection of embeddable elements using eclipselink
  • isotope shakes after the transition
  • How to Include CSS style when converting svg to png
  • Reshape dataframe to dataframe with unlimited rows and filling zeroes where no values
  • Salesforce consuming XML and display data in Visualforce report
  • Xamarin.Forms: How To Populate A Pie Chart From Web API Data?
  • Train two models concurrently
  • Windows 8 Flexboxes - Nesting Flexboxes with overflow enabled
  • Tableview make specific cell or row editable
  • Microsoft bot framework webchat C#
  • How to create wsdl from xsd
  • Calculate savings percentage for house down payment in 36 months
  • Synchronous Calls with jqGrid?
  • ASP.NET MVC 2 actions for the same route?
  • How to include associated objects using gon in Rails/jQuery
  • how do i compare two rows and store the similarities of the two rows in another column
  • How to clear a browser cache in Protractor
  • php “page caching” solution suggestions for CMS Applications
  • Jersey serializes character value to ASCII equivalent numeric string
  • CAS 4 - Not able to retrieve the LDAP groups after successful authentication
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?