?
Solved

SQL align query problem

Posted on 2008-11-12
2
Medium Priority
?
400 Views
Last Modified: 2012-05-05
I have the following query:

The first part of the query works fine however i need to align the second query into the same results.  The only condition in the 2nd query is the sitename.  I need it to pull all the results from T2.[CardName] where it contains data and align it with the first query.
SELECT  T2.U_IIS_INADR, T0.[Dscription], T0.U_iis_ddate, T1.[SlpName], T0.U_IIS_EXT as Work_Requiring_attention FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
 
UNION ALL
 
SELECT  T2.U_IIS_INADR, T0.[Dscription], null, null, T0.U_IIS_EXT as Work_Requiring_attention 
FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
AND T0.U_IIS_EXT is not Null

Open in new window

0
Comment
Question by:KeirMcCann
2 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22939999
SELECT  T2.U_IIS_INADR, T0.[Dscription], T0.U_iis_ddate, T1.[SlpName], T0.U_IIS_EXT as Work_Requiring_attention
FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
 
UNION ALL
 
SELECT  T2.U_IIS_INADR, T0.[Dscription], cast(null as datetime), cast(null as varchar(max), T0.U_IIS_EXT as Work_Requiring_attention
FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
AND T0.U_IIS_EXT is not Null
0
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22940528

What do you mean as align the second query into the same results?
I have noticed your 2nd query is a subset of the previous (your first query already has the data).
UNION ALL allows you to have duplicate values, and you UNION ALL meet the criteria: since your SELECT statement within the UNION ALL has the same number of columns. Your columns also have similar data types. Also, the columns in each SELECT statement must be in the same order.
If you need to sort your union  you can us sub-query your Union all statement like:
SELECT * FROM
(       SELECT column_name(s) FROM table_name1
        UNION ALL
        SELECT column_name(s) FROM table_name2
)  T
ORDER BY T.column_name
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question