6857

Create a virtual column to verify if data exists on anothers

Question:

<strong>mt_user</strong>:

usr_id usr_login usr_passwd 1 user1 user1 2 user2 user2 3 user3 user3 4 user4 user4

<strong>mt_settings</strong>:

sttgs_id sttgs_description 1 Setting 1 2 Setting 2 3 Setting 3 4 Setting 4 5 Setting 5

<strong>mt_user_settings</strong>:

uss_id usr_id sttgs_id 1 1 1 2 1 2 3 2 5 4 3 2 5 3 3 6 3 5 7 4 4

What I need to show is data like this:

WHERE usr_id='2' :

sttgs_id sttgs_description has it? (virtual_column) 1 Setting 1 0 2 Setting 2 0 3 Setting 3 0 4 Setting 4 0 5 Setting 5 1

WHERE usr_id='3' :

sttgs_id sttgs_description has it? (virtual_column) 1 Setting 1 0 2 Setting 2 1 3 Setting 3 1 4 Setting 4 0 5 Setting 5 1

How could I do it correctly?

Answer1:

SELECT s.sttgs_id, s.sttgs_description, IF(st.uss_id,1,0) as `has it` FROM mt_user u JOIN mt_settings s LEFT JOIN mt_user_settings us ON us.usr_id = u.usr_id AND us.sttgs_id = s.sttgs_id WHERE u.usr_id = 2

you can also do it without the users table

SELECT s.sttgs_id, s.sttgs_description, IF(st.uss_id,1,0) as `has it` FROM mt_settings s LEFT JOIN mt_user_settings us ON us.usr_id = 2 AND us.sttgs_id = s.sttgs_id

Recommend

  • How does user1 (admin) submit presence subscription request from user2 to user 3 [closed]
  • How to set a value's for calculating Eucludeian distance and correlation
  • How can I get kermit script to accept arguments and connect to serial device
  • Javascript: Fetch list of usernames to console/file
  • postgres aggregate join matches to multiple array fields while creating views
  • how to return response using mongo's aggregate?
  • asp.net How do I reference authorized users hard coded in web.config in my code
  • Linq query to compare 2 List for distinct matches
  • How to Merge two lists
  • Usage of DatasetUtilities.createCategoryDataset in jfreechart in java
  • Skipping a multidimensional Array in a foreach loop PHP
  • Is there a way to query if array field contains a certain value in Doctrine2?
  • Restricting file access to certain users
  • postgres aggregate join matches to an array field
  • How to watch a variable in pudb?
  • PHP: setting session variables through variable variables
  • How can I increment one line at a time from a text file and sed the output to another file?
  • How to return large Json in Asp.Net Core
  • Take column of string data in pandas dataframe and split into separate columns
  • How could this be done with multi-threading?
  • How to integrate a custom GraphicsItem into a QML scene?
  • How to edit a Word document that opens in read-mode in VBA
  • Magento “Please specify the product's option(s)” message remains after completing add to cart a
  • IllegalArgumentException: The servlets named [HelloWorlds] and [Hello] are both mapped to the url-pa
  • How to convert WPF project so it can be used as a class library by a separate exe
  • Parse Framework with Swift
  • How to make SASS put relative paths in its output
  • Consuming a web service with the Netbeans Platform
  • How to protect an asp:textbox from user input?
  • Fat binaries in iOS
  • CloseOptionsMenu doesn't work?
  • MultiLevel ExpandableListView not highlighting properly
  • Efficient algorithm to find additions and removals from 2 collections
  • How do I get the list of bad records that didn't load in Bigquery?
  • Ember.js model to be organised as a tree structure
  • Thread safety of a fluent like class using clone() and non final fields
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Linking SubReports Without LinkChild/LinkMaster