Re: Combining Queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 16:12:58 -0400

Mike,

  If you can get some results one way, and the rest another way, and you
want all of them, you probably want a UNION or UNION ALL query.

select ...
UNION ALL
select ...

If you need to eliminate rows that appear more than once, whether within
one half of the query or because they appear uniquely in each half, use
UNION, which will eliminate all duplicate rows.

Steve Kass
Drew University

Mike Perry wrote:

>OK, I give up, there has to be a simple answer to this but I've dented the
>wall banging my head into it trying to figure it out.
>
>The following Query works as expected and the result set is what is desired
>(13338 records):
>
>SELECT *
>FROM OPENQUERY(wcw, 'SELECT Cust_Nbr,
> Order_Nbr,
> Entry_Date,
> Invoice_Nbr,
> Line_Nbr,
> Item_Nbr,
> Price,
> Qty_Ord,
> Price_Extension
> FROM oehistdetail') AS wcw,
> (SELECT C1.U_Key3 AS Cust_Nbr
> FROM Contact2 AS C2,
> Contact1 AS C1,
> OPENQUERY(wcw, 'SELECT Cust_Nbr
> FROM Customers') AS Custs
> WHERE C2.AccountNo=C1.AccountNo
> AND C2.UVQ2CLASSI = 'Business Growth'
> AND C1.U_Key3 <> ''
> AND C1.U_Key3=Custs.Cust_Nbr) AS GrowthCusts
>WHERE (entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
> AND UPPER(RIGHT(Item_Nbr, 4)) <> 'RNDM'
> AND Price_Extension <> 0
> AND Item_Nbr <> '@FREIGHT'
> AND wcw.Cust_Nbr=GrowthCusts.Cust_Nbr)
>ORDER BY Entry_Date, Order_Nbr
>
>And the following also returns what is desired (1027 records):
>
>SELECT *
>FROM OPENQUERY(wcw, 'SELECT Cust_Nbr,
> Order_Nbr,
> Entry_Date,
> Invoice_Nbr,
> Line_Nbr,
> Item_Nbr,
> Price,
> Qty_Ord,
> Price_Extension
> FROM oehistdetail')
>WHERE (entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
> AND UPPER(RIGHT(Item_Nbr, 4)) = 'RNDM')
>ORDER BY Entry_Date, Order_Nbr
>
>But when I combine these two queries by adding the last two lines as follows
>I don't get the expected 14,365 records, instead I get several million with
>duplicates:
>
>SELECT *
>FROM OPENQUERY(wcw, 'SELECT Cust_Nbr,
> Order_Nbr,
> Entry_Date,
> Invoice_Nbr,
> Line_Nbr,
> Item_Nbr,
> Price,
> Qty_Ord,
> Price_Extension
> FROM oehistdetail') AS wcw,
> (SELECT C1.U_Key3 AS Cust_Nbr
> FROM Contact2 AS C2,
> Contact1 AS C1,
> OPENQUERY(wcw, 'SELECT Cust_Nbr
> FROM Customers') AS Custs
> WHERE C2.AccountNo=C1.AccountNo
> AND C2.UVQ2CLASSI = 'Business Growth'
> AND C1.U_Key3 <> ''
> AND C1.U_Key3=Custs.Cust_Nbr) AS GrowthCusts
>WHERE ((entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
> AND UPPER(RIGHT(Item_Nbr, 4)) <> 'RNDM'
> AND Price_Extension <> 0
> AND Item_Nbr <> '@FREIGHT'
> AND wcw.Cust_Nbr=GrowthCusts.Cust_Nbr))
> OR ((entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
> AND UPPER(RIGHT(Item_Nbr, 4)) = 'RNDM'))
>ORDER BY Entry_Date, Order_Nbr
>
>Basically what I have to do is arrive at a result set that includes all the
>order detail records which were purchased by customers that are tagged as
>"Business Growth" (which could also include "random" sales) and all "random"
>sales whether they are "business growth" tagged or not since the first of
>the year.
>
>I know that the OPENQUERY portions appear to be less than optimized, but in
>some cases the only way I can get the result set I want from the Linked
>Server (an ODBC driver connection to a COBOL dataset on a Linux box) is by
>querying the entire dataset and then apply constraints once the SQL server
>has the dataset from the Linked Server (for instance, UPPER and RIGHT are
>not recognized by the ODBC driver).
>
>The Contact1 and Contact2 tables are in a Database called "Windy" and the
>order detail data is on the Linked Server which is referred to as "wcw."
>
>Any help would be greatly appreciated!
>
>Thanks in advance.
>
>Mike
>
>
>
>

Relevant Pages

  • Re: Cannot post string "union select" php form
    ... i am trying to figure it out why i cannot post string "union select" ... every time i try to post data which content union and select .. ... i guess the server i am using has some kind of filter and if a post ... Do you have some PHP code showing this? ...
    (comp.lang.php)
  • Re: Nochmal SP mit Records-Set Rückgabe.
    ... daß MySQL derart kapott ist. ... Server neu gestartet wird oder ein berechtigter Benutzer selbige ... und auch UNION ALL wirft einen ...
    (de.comp.datenbanken.mysql)
  • Re: Cannot post string "union select" php form
    ... i am trying to figure it out why i cannot post string "union select" ... every time i try to post data which content union and select .. ... i guess the server i am using has some kind of filter and if a post ... so i think the problem is with string which i am posting ... ...
    (comp.lang.php)
  • Re: Cannot post string "union select" php form
    ... i am trying to figure it out why i cannot post string "union select" ... every time i try to post data which content union and select .. ... i guess the server i am using has some kind of filter and if a post ... Do you have some PHP code showing this? ...
    (comp.lang.php)
  • Establishing Precedence In ORDERBY Condition Causing Problems.
    ... from Customers withunion select distinct user_name ... from CustomerPhotos withunion select distinct email_address ... Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID ... Left Join SavedSearches tab6 on (drv.emailAddress = ...
    (comp.databases.ms-sqlserver)