logo
down
shadow

mysql query logic for fetching data from 3 related tables on certain condition


mysql query logic for fetching data from 3 related tables on certain condition

By : Joey Ho
Date : November 22 2020, 02:59 PM


Share : facebook icon twitter icon
mySQL select query, get data from multiple tables with some logic

mySQL select query, get data from multiple tables with some logic


By : S.M.A. Sharif
Date : March 29 2020, 07:55 AM
To fix the issue you can do You want to use an outer join in combination with the coalesce() function (which returns the first non-null value in the selected columns) like this:
code :
SELECT 
    a.aid, 
    a.is_activated, 
    a.user_id, 
    coalesce(b.user_name, a.user_id, 0) as User
from 
    activation_details a
        left outer join users b
            on a.user_id=b.uid
mysql> select * from first
    -> ;
+------+-------+
| id   | title |
+------+-------+
|    1 | aaaa  |
|    2 | bbbb  |
|    3 | cccc  |
+------+-------+
3 rows in set (0.00 sec)

mysql> select coalesce('a', id) from first
    -> ;
+-------------------+
| coalesce('a', id) |
+-------------------+
| a                 |
| a                 |
| a                 |
+-------------------+
3 rows in set (0.00 sec)

mysql> select coalesce('a', 1) from first;
+------------------+
| coalesce('a', 1) |
+------------------+
| a                |
| a                |
| a                |
+------------------+
3 rows in set (0.00 sec)

mysql> select coalesce(null, 1) from first;
+-------------------+
| coalesce(null, 1) |
+-------------------+
|                 1 |
|                 1 |
|                 1 |
+-------------------+
3 rows in set (0.00 sec)
Fetching data from related tables

Fetching data from related tables


By : klangrud
Date : March 29 2020, 07:55 AM
get 'related' data from several tables with a single query in PHP/MySql?

get 'related' data from several tables with a single query in PHP/MySql?


By : user3342698
Date : March 29 2020, 07:55 AM
I hope this helps . I've found a way to achieve what I wanted, by combining the answers posted by @Adrian and @Daniel who pointed me in the right direction (thanks!) and adding the use of CONCAT, GROUP_CONCAT and CAST (to mix int and varchar).
I upvoted both answers, which solved the problem partially, but I thought I should post my own answer since (I think) is a cleaner solution, at least for what I wanted, and it may help someone else.
code :
SELECT 
  a.id, 
  a.title, 
  GROUP_CONCAT( DISTINCT CAST( t.id AS CHAR( 50 ) ) ,  ':', t.name ORDER BY t.id SEPARATOR  ',' ) AS  'tags', 
  GROUP_CONCAT( DISTINCT CAST( i.id AS CHAR( 50 ) ) ,  ':', i.fileName ORDER BY i.id SEPARATOR  ',' ) AS  'images'
FROM 
  articles a
JOIN 
  article_tags ta ON a.id = ta.articleId
JOIN 
  images i ON a.id = i.articleId
JOIN 
  tags t ON ta.tagId = t.id
WHERE 
  a.id = :id
Pulling data from two tables (for a 'related products' query) in MySQL

Pulling data from two tables (for a 'related products' query) in MySQL


By : Lars Fredrik Lunde
Date : March 29 2020, 07:55 AM
hope this fix your issue I'm trying to pull data from two tables for a 'related products' widget. I've tried all the JOINS and UNIONS I can and still get nothing. , You can use a simple INNER JOIN for this, e.g.:
code :
SELECT pd.pdid, pd.pdname
FROM   productdocs pd JOIN prodrelated pr ON pd.pdid = pr.pritemid
WHERE  pd.prprodid = <any_id>;
Facing issues while fetching data from two different mysql tables using a single query..!

Facing issues while fetching data from two different mysql tables using a single query..!


By : user2551348
Date : March 29 2020, 07:55 AM
I hope this helps . Although your query is working, it is not at all efficient because it uses an implicit cross join which results in a very large resultset.
Use a proper INNER JOIN with a condition applied with WHERE:
code :
SELECT u.*, p.* 
FROM tbl_users u INNER JOIN posts p
ON u.id = p.user_id_p
WHERE u.id = ?
ORDER BY p.post_id DESC
Related Posts Related Posts :
  • How to sort varchar id on basis integer in it using mysql
  • Trial Balance Update accounts
  • Always return user details as json every time user login in laravel 5
  • join table on postgres with php
  • How to check for empty values with in_array WITH EXCEPTIONS?
  • How to replace return carriage in json string made from mysql and php?
  • Intl extension not getting loaded
  • PHP How to echo a 3-dim array
  • How to extend and modify a vendor model in Laravel 5?
  • How to change the number of posts that Wordpress JSON Api loads per page
  • how to validate dynamic text boxes using jquery
  • PHP undefined index id error
  • How to Parse xml data using php
  • getJSON parser cannot process JSON output from PHP file
  • Using Redis on Azure with Symfony2
  • php proc_open file descriptor in bash command
  • Trying to create a generalized function in PHP 5.2, error message not showing
  • Merging multiple rows with same field in column
  • Move "in stock" up on Woocommerce product page
  • Wrong reference path to Mail.php
  • phalcon volt bitwise operatios?
  • How to switch between tab when form submitting in same php file?
  • wp all import script to custom post image gallery
  • Storing a single password within in a PHP file instead of a database of users
  • get increment decrement day once clicked button
  • sql check for duplicates returning bool false?
  • Webmaster tools api dimensions
  • Seeding table using model factory not saving
  • How do I save associated entities in Doctrine 2?
  • fopen $_SERVER[REQUEST_URI]
  • RedBean One to Many Attribute Not Working
  • Safely passing ids to form action?
  • Php array explode a char
  • PHP MySQL Login Failure
  • can't get file_exists to work
  • Array PHP, Check if exist multiple elements to another array
  • Can array_walk be nested in php?
  • Notice: Use of undefined constant DIR - assumed '_DIR_'
  • Getting a weird 500 error with ajax
  • How can I filter an array of strings to remove elements that are prefixes of other elements?
  • Duplicate entries when assigning custom id to Laravel Model
  • AJAX call not returning php file command
  • How to make "INNER JOIN" sql query based on my requirements
  • Routes by DNS names in Symfony2 project
  • Laravel 5.1 Mysql NULL
  • Why special characters like = or " break PHP regexp when using \b word boundary?
  • Login System with a DB Connection Class don't login. Return with else error for invalid login
  • Laravel 4.2 Simple Session Logic not working
  • PHP : get timezone where time is as specified
  • PHP Override an array on file
  • How to upgrade from drush 7 to drush 8 via Composer
  • Dynamic Modal not displaying in table
  • Can I have multiple sessions at the same in PHP?
  • PHP Multiple Filtering Links
  • PHP - XLSX to CSV adding spaces
  • mySQLi SELECT in PHP with another table's IVs for decrypting table column data
  • Insert Text Field Name or ID as Value in MySQL Column
  • How to record the most common time online of a group of users?
  • Switch from mySQL to mariaDB timestamp messup
  • Generating all 6 character alphanumeric combinations (upper and lower case)
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co