Combining three queries from three tables all on one PK - Need Help!

Database
Enthusiast

Combining three queries from three tables all on one PK - Need Help!

Afternoon!

I have three queries. Q1 has col1....col14 from Table1 where x and xx and xx. Q2 has col1-col5 from Table2 where x and xx, and Q3 has col1....col2 (concatenated w/2 columns)...col3 from Table3 where x.

 

I am trying to recreate this to make a single SQL query. Currently, this is an Access routine that needs written for automation. Q1 creates Table 1, Q2...Table2 and Q3...Table3.

Those are the first three steps of my Access query.

Step 4 appends Q3 into Q2 on ACCT_KEY_X, and in Q3 it's actually the concatenation of two columns whos result wind up in 3 more digits than ACCT_KEY_X. I am essentially trying to mash Q3 into Q2, then combine that result into Q1 without losing data. All within one query.

 

The final result is just a simple List report (excel sheet) with columns across the top and data filling in throw rows by date. Sounds simple, but I am stuck. Any help would be most appreciated! Thank you!


Accepted Solutions
Apprentice

Re: Combining three queries from three tables all on one PK - Need Help!

Hi,

 

Initially I'd say use UNION/UNION ALL to combine the results of each query. That may work, it really depends on the details of how you want to 'combine' the data from Q1, Q2 etc.

 

To start with, something like:

SELECT col1
      ,col2
      ,col3
      ,col4
      ,CAST(NULL AS dt) AS col5
FROM t1
WHERE ...
UNION ALL
SELECT col1
      ,col2
      ,col3
      ,col4
      ,col5
FROM t2
WHERE ...

Because you're using set operators (the UNION or UNION ALL) the column list in each SELECT must have the same number of columns and the corresponding column in each select list must have compatible data types - hence the ",CAST(NULL AS dt) AS col5". In this case the 'dt' must be a datatype compatible with the data type of 'col5'.

 

Also, any ORDER BY clause at the end must use numeric column position numbers, not column names.

 

Extend this idea for the other 3 queries.

 

Does that get you started?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
1 REPLY
Apprentice

Re: Combining three queries from three tables all on one PK - Need Help!

Hi,

 

Initially I'd say use UNION/UNION ALL to combine the results of each query. That may work, it really depends on the details of how you want to 'combine' the data from Q1, Q2 etc.

 

To start with, something like:

SELECT col1
      ,col2
      ,col3
      ,col4
      ,CAST(NULL AS dt) AS col5
FROM t1
WHERE ...
UNION ALL
SELECT col1
      ,col2
      ,col3
      ,col4
      ,col5
FROM t2
WHERE ...

Because you're using set operators (the UNION or UNION ALL) the column list in each SELECT must have the same number of columns and the corresponding column in each select list must have compatible data types - hence the ",CAST(NULL AS dt) AS col5". In this case the 'dt' must be a datatype compatible with the data type of 'col5'.

 

Also, any ORDER BY clause at the end must use numeric column position numbers, not column names.

 

Extend this idea for the other 3 queries.

 

Does that get you started?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com