logo
down
shadow

subquery with join in same table in mysql


subquery with join in same table in mysql

By : user2172014
Date : October 24 2020, 08:10 PM
fixed the issue. Will look into that further I am trying to fetch some data from my table using join and subquery. My scenario is: I have a table table ABC, with structure as shown , You can have one more self-join to get the Spouse name.
code :
SELECT E1.name AS memname,
       E2.name AS parentname,
       E3.name AS spouse 
FROM ABC AS E1 
LEFT OUTER JOIN ABC AS E2 ON E2.id = E1.parent_id  
LEFT OUTER JOIN ABC AS E3 ON E3.parent_id = E1.id AND 
                             E3.relation = 'Spouse'   
where E1.relation <> 'Spouse'


Share : facebook icon twitter icon
In SQL or MySQL, can we join a table and a subquery result?

In SQL or MySQL, can we join a table and a subquery result?


By : Martin Kalliomäki
Date : March 29 2020, 07:55 AM
Does that help yes, sql works on sets, a subquery returns a set as result, so this is possible.
you have to give the subquery a name: (select * from table) as sub
In mysql how do I add another table join within a subquery?

In mysql how do I add another table join within a subquery?


By : ravi kiran
Date : March 29 2020, 07:55 AM
seems to work fine This queries are semantically the same. But remember either you put the table in the join or in the from clause:
With explicit joins:
code :
select s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive
from storage s
join status t on t.id = s.status_id
join unit u1 on s.cache_unit_id = u1.id
join unit u2 on s.drive_unit_id = u2.id
select s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive
from storage s, status t, unit u1, unit u2
where t.id = s.status_id and s.cache_unit_id = u1.id and s.drive_unit_id = u2.id
select s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive,
u3.value * s.error_value as Error,
u3.value * s.error_value * 2 as ErrorHigh,
u3.value * s.error_value - u3.value * s.error_value as ErrorLow,
m.area
from storage s
join status t on t.id = s.status_id
join manufac m on m.id = s.area
join unit u1 on s.cache_unit_id = u1.id
join unit u2 on s.drive_unit_id = u2.id
join unit u3 on s.error_unit_id = u3.id
+----------+-------------+----------+--------------+---------+-----------+----------+-------+
|  TITLE   | DESCRIPTION |  CACHE   |    DRIVE     |  ERROR  | ERRORHIGH | ERRORLOW | AREA  |
+----------+-------------+----------+--------------+---------+-----------+----------+-------+
| Seagate  | Durka Durka |   400000 | 100000000000 |    1000 |      2000 |        0 | US    |
| Scansoft | Blah.. Blah | 80000000 | 250000000000 | 1000000 |   2000000 |        0 | CHINA |
+----------+-------------+----------+--------------+---------+-----------+----------+-------+
Need data from couple of table in mysql using join or subquery

Need data from couple of table in mysql using join or subquery


By : Cassandra
Date : March 29 2020, 07:55 AM
around this issue Instead of subqueries, the more intuitive approach would be to join and group by the account. Note that you'd need to treat credit as positive number and debit as negative:
code :
SELECT   ab.name, SUM(pq.credit - pq.debit) AS balance
FROM     ab
JOIN     pq ON ab.id = pq.ab_id
GROUP BY ab.name
MYSQL updating a table containing a join and subquery

MYSQL updating a table containing a join and subquery


By : wjw0706
Date : March 29 2020, 07:55 AM
I wish did fix the issue. You are not able to do both in a single query. Typically one would run a "select query" to inspect if the desired logic appears correct, e.g.
code :
SELECT
        p.person_id
      , p.name_first
      , p.name_last
      , ep.start_date
      , curdate() as today_date
      , TIMESTAMPDIFF(month,ep.start_date,curdate()) as duration_months 
FROM employee ep
INNER JOIN person p ON ep.person_id = p.person_id 
WHERE ep.start_date < curdate() - INTERVAL 24 MONTH
;
UPDATE employee ep 
SET monthly_salary = monthly_salary*1.15 
WHERE ep.start_date < curdate() - INTERVAL 24 MONTH
;
where TIMESTAMPDIFF(month,ep.start_date,curdate()) > 24
WHERE ep.start_date < curdate() - INTERVAL 24 MONTH
MySQL subquery fetch only latest value from JOIN table

MySQL subquery fetch only latest value from JOIN table


By : Ray
Date : March 29 2020, 07:55 AM
Does that help I have the following MySQL query. The user_extra table can return multiple values for a given user_id. I would like only the latest a.session_date value from user_extra.
code :
select ap.user_id, ap1.value
from user_reg ap
inner join (
    select user_id, max(session_date) as MaxDate, value
    from user_extra 
    group by user_id
) ap1 on ap.user_id = ap1.user_id and ap.date = ap1.MaxDate
Related Posts Related Posts :
  • How to setup local server for wordpress site with git
  • Multiplying CASE row with different values
  • Index on a table not being used all the time
  • How can I get a date from mysql database if it is not null?
  • How to execute a TRIGGER in MSSQl?
  • MySQL 8 Window Functions + Full-text searching
  • Join on large table getting slower
  • Select record from two different table
  • Getting 1064 error while creating mysql trigger
  • MySQL Database Operations
  • How can I make this SQL sort by most relevent?
  • Database query on month
  • Select number of matching rows of a particular column in MySQL
  • How to use rake database commands with password
  • If we change a primary key value, why don't we have to change a dependent column value?
  • MySQL - selecting all records except the ones already associated in the relational table
  • Delete Duplicate MySQL rows but keep one
  • ORDER BY does not perform any function
  • Update column with output of select within the same table in mysql
  • Set a variable inside case statement in mysql
  • MYSQL: How to get rows inserted in the last X hours without querying the entire table
  • AWS RDS MySQL Cross-region replication
  • Cannot truncate a table referenced in a foreign key constraint from empty table
  • How does SQL determine a character's length in a varchar?
  • MySQL : Getting DB row with exact same data from a vector
  • Mysql update query with join
  • Group values that have the same name in one column and same id in other column
  • Mysql query syntax for conditional inserts
  • Is it faster to run an SQL count(*) query in a loop, or try to merge it into the parent query?
  • MySQL query to fetch product variants
  • Report Reindex taking too long after destroy
  • MySQL - Adding varchar as a foreign key
  • How to find duplicated entries which has different slug?
  • SQL Query comparing values in different rows
  • Eloquent giving error but query executes fine in phpmyadmin
  • JPQL fetch data from multiple tables
  • MySql Query - Expanding data into new table
  • Official MySQL Docker container not caching queries?
  • MySQL, Count values in every fields in a table
  • SQL : number different dates within users
  • elastic beanstalk docker app cannot connect to mysql
  • Why am I getting errno: 150 "Foreign key constraint is incorrectly formed"?
  • Update columns which included in payload
  • MySQL subquery in select
  • Get difference in top 2 salary of employee from each department
  • connecting to database using RMySQL and .my.cnf file in R
  • How to get data by mysql
  • SQL Error [1054] [42S22]: Unknown column ' ' in 'field list'
  • turn c# code into mysql function
  • How to loop through and output nested array in Laravel
  • MySQL TRIM spaces inside text
  • Mysql query with multiple selects results in high CPU load
  • Backup DB Django MysqlDump
  • How to select all rows from group, until occurrence of a value
  • Using substring to filter a specific word from a string in MySQL
  • Mysql - Alias in Left Outer Join giving error
  • How use custom alias field from select fields in join?
  • How to sum durations in units of Year, Month and Day in MySQL?
  • Is it possible to assign the values from select exist query in MySQL to multiple variables in a stored procedure?
  • How does mysql resolves conflict when same option is configured twice?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org