How to join 2 select query in Access DB 2013

Question

0.00/5 (No votes)

See more:

Hi How to join 2 select statement from same table,each select statement have subqery that i pass in where condition.

Example:

SQL

UserId  PDate             PName   PWeight BMI    Intake
1     05/08/2015 15:58    Aravind 56     26.63   630
1     06/08/2015 15:59    Aravind 55     35.77   870
1     08/08/2015 16:01    Aravind 44     18.08   690
1     10/08/2015 16:02    Aravind 54     30.07   240
1     12/08/2015 16:03    Aravind 66     31.39   170
1     14/08/2015 16:04    Aravind 56     26.63   1180
1     16/08/2015 16:19    Aravind 34     16.17   180

First query and result is

SQL

select
  UserId as FUserID,PName AS FPName,PWeight as FVW,BMI as FVBMI,Intake as FVIN ,PDate AS FPDate
from
  BodyMeasurements where PDate=(select
  min(PDate)
from
  BodyMeasurements
group by UserId)


FUserID	 FPName	    FVW	FVBMI	FVIN	FPDate
1	     Aravind	56	26.63	630	    05/08/2015 3:58:39 PM

Second query and result is

SQL

select
  UserId as LUserID,PName AS LPName,PWeight as LVW,BMI as LVBMI,Intake as LVIN,PDate AS LPDate
from
  BodyMeasurements where PDate=(select
  Max(PDate) 
from
  BodyMeasurements
group by UserId)


LUserIS	LPName	 LVW	LVBMI	LVIN	LPDate
1	    Aravind	 34	    16.17	180	    16/08/2015 4:19:39 PM

So finally i need to join both query as single query using join or union.How achieve this.
If i use union or union all i can get result in 2 rows,but i need to get single row with different names,
like FUserID form first query and LUserID from 2nd query

Thanx

Posted 6-Oct-15 6:52am

Aravindba

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-10-06T07:11:00

Solution 1

If you want to get min and max value of PDate for sigle user, try this:

SQL

SELECT UserId, MIN(PDate) AS LPDate, MAX(PDate) AS FPDate
FROM BodyMeasurements 
GROUP UserId

[EDIT]
Using UNION ALL[^]:

SQL

SELECT UserId, PName, PWeight, BMI, Intake, MIN(PDate)
FROM BodyMeasurements
GROUP BY UserId, PName, PWeight, BMI, Intake
UNION ALL
SELECT UserId, PName, PWeight, BMI, Intake, MAX(PDate)
FROM BodyMeasurements
GROUP BY UserId, PName, PWeight, BMI, Intake

It is possible to get data using Joins[^], but i need to warn you: MS Access likes brackets when there's more than one join. Please see: How to: Perform Joins Using Access SQL[^]

[EDIT]
As to the OP's comments...

SQL

SELECT t1.*, t2.*
FROM (
     -- your first query
    (
     -- your second query
    ) AS t2 ON t1. UserId = t2.UserId

Posted 6-Oct-15 7:11am

Maciej Los

Updated 6-Oct-15 20:05pm

v3

Comments

CPallini 6-Oct-15 16:06pm

5.

Maciej Los 6-Oct-15 16:11pm

Thank you, Carlo.

Aravindba 6-Oct-15 20:17pm

Hi pls see my description and 2 separated query,i added where condition for PDate, that Pdate meets the result one from minimum date and another one from maximum date,
If i use your union query i get 14 rows,but i need to get in only one row with different column names like FuserID,FPName,LUserID,LPName.....

And i don't want min and max date for user,i need to get Pweigth,BMI that form minimum date and Pweigth,BMI from maximum date ,

For example here Pweight=56,BMI=26.63 which from minimum Pdate (05/08/2015) and Pweight=34,BMI=16.17 which from Pdate (16/08/2015) is maximum.

Maciej Los 7-Oct-15 2:02am

I do not understand based on what condition you want to unon/join data. As per your logic, union is impossible, because union uses the same column names.