How I get common values from two query in a table and join it with another table in MySql?

I have two tables. One is orders and another is users. I want run two different query on orders table and want to get the common values and join it with users table.

    orders
    ======
    +----+---------+------------+------------+--------------+
    | id | user_id | receive_id | arrival_id | departure_id |
    +----+---------+------------+------------+--------------+
    | 1  |   1     |     1      |    null    |      5       |
    +----+---------+------------+------------+--------------+
    | 2  |   2     |     1      |      3     |      6       |
    +----+---------+------------+------------+--------------+
    | 3  |   3     |     1      |    null    |      7       |
    +----+---------+------------+------------+--------------+
    | 4  |   1     |     3      |      5     |    null      |
    +----+---------+------------+------------+--------------+
    | 5  |   3     |     3      |      6     |    null      |
    +----+---------+------------+------------+--------------+
    | 6  |   4     |     3      |      7     |    null      |
    +----+---------+------------+------------+--------------+
    | 7  |   1     |     4      |      7     |      9       |
    +----+---------+------------+------------+--------------+
    users
    =====
    +----+---------+
    | id |   name  |
    +----+---------+
    | 1  |  Shaon  |
    +----+---------+
    | 2  |  Rabu   |
    +----+---------+
    | 3  |  Asha   | 
    +----+---------+

1st Query..

SELECT DISTINCT user_id from orders where receive_id = 1 and departure_id != 'null'
Its result will be..
1 2 3

2nd Query..

SELECT DISTINCT user_id from orders where receive_id = 3 and arrival_id != 'null'
Its result will be..
1 3 4

I want get common values form those queries

That is ..
1 3

When I Join it to users table then result will be
_id__name__arrival_id__departure_id
 1  Shaon    null           5
 3  Asha     null           7
But now I want to see..
_id__name__arrival_id__departure_id
 1  Shaon     5             5
 3  Asha      6             7
on arrival_id row the value from where 
receive_id = 3 there's arrival_id as distinct user_id

I think code will be like bellow. But It's not working.
SELECT DISTINCT user_id from orders where receive_id = 1 and departure_id != 'null'
COMMON
SELECT DISTINCT user_id from orders where receive_id = 3 and arrival_id != 'null'
JOIN users on (users.id = orders.user_id)

You will get what you need by putting one query as a subquery for the other. Something like this,

SELECT DISTINCT user_id from orders
JOIN users on (users.id = orders.user_id)
where receive_id = 1 and departure_id != 'null'
and user_id in (SELECT DISTINCT user_id from orders where receive_id = 3 and arrival_id != 'null')

Hope this helped.

MySQL UNION: Combining Results of Two or More Queries, To combine result set of two or more queries using the UNION operator, these KEY ); CREATE TABLE t2 ( id INT PRIMARY KEY ); INSERT INTO t1 VALUES� You need to ask a question that requires information from both tables. If you do an inner join with the following query: SELECT * FROM Product,Color WHERE Product.Name = Color.Name. you get the following results table with four columns: Name (from Product ), Type, Name (from Color ), and Color.

Try this query,

select orders.id, users.id from orders inner join users 
on orders.user_id = users.id

Combine Information from Multiple MySQL Tables with JOIN , Tables are combined by matching data in a column — the column that they have in common. The two common types of joins are an inner join and an outer join. You use different SELECT queries for an inner join and the two types of outer joins you get the following results table with four columns: Name (from Product) ,� UNION in MySQL is used to union multiple columns from different table into a single column. The structure of UNION query for selecting unique values is: SELECT column_name(s) FROM table1. UNION. SELECT column_name(s) FROM table2; And for selecting repeated values from columns is: SELECT column_name(s) FROM table1. UNION ALL. SELECT column_name(s) FROM table2;

SELECT DISTINCT user_id from orders JOIN 
users on (users.id = orders.user_id) where receive_id = 3 and 
arrival_id != 'null' AND user_id IN (SELECT DISTINCT user_id from 
orders   where receive_id = 1 and departure_id != 'null');

You can use this query to fetch records

Retrieving Records from Multiple Tables, Get down and dirty with mySQL by learning the basics of SQL syntax and out how to write a query is determining the proper way to join tables together. Another common type of subselect query searches for values in one� The Full Join. If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, MySQL performs a full join. For example, if you join t1 and t2 as follows, each row in t1 is combined with each row in t2: mysql> SELECT t1.*, t2.*.

How to Join Two Tables in MySQL, Now let us get started by joining these two tables. You can also fill your The structure of UNION query for selecting unique values is: SELECT� We can update another table with the help of inner join. Let us create two tables. Creating a table mysql> CREATE table tblFirst -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.48 sec)

SQL joins and how to use them, JOINs are clauses in SQL statements that link two tables together, usually To join one table to another, PostgreSQL needs to know several pieces of information: Even so, it is still common to refer to this type of join as an 'outer' join in order You may have noticed that some of the queries we list above can get a bit long� At first, we will analyze the query. An inner join clause that is between onlinecustomers and orders tables derived the matched rows between these two tables. The second inner join clause that combines the sales table derived the matched rows from the previous result set. The following colored tables illustration will help us to understand the

Learn How to Combine Data with a CROSS JOIN, A common use for a cross join is to create obtain all combinations of items, such as Cross joins are used to return every combination of rows from two tables, this If you CROSS JOIN a table of 1,000 rows with another of 1,000 you end up with and read, I used CTE's (Common Table Expressions) to create this query. A derived table can be referenced only a single time within a query. A CTE can be referenced multiple times. To use multiple instances of a derived table result, you must derive the result multiple times. A CTE can be self-referencing (recursive). One CTE can refer to another.

Comments
  • Thanks for your reply. But you missed to Join with users table.
  • The following code returns the distinct column user_id.Beside these If I want to see all column then what I will do?
  • If you want to see all columns, you will have to select * instead of select distinct user_id. You should not use distinct user_id here in this case since the other column's values will be different for same user in different rows.
  • If you want to select all columns of a specific table, then you can use select tablename.*. Eg: select orders.* from or select users.* from
  • I want distinct value in terms of user_id but also want all column then what I will do?
  • Why should the OP try this? Please add an explanation of what you did and why you did it that way not only for the OP but for future visitors to SO.