sql query (UNION) help!!! Thanks

sql query (UNION) help!!! Thanks

Post by Dian » Sun, 30 Mar 2003 00:08:20



I have following query which use UNION but I need to
create a query which return the same result but without
UNION. Please Help. Thanks!!!

SELECT    
        VD1.AcctNum FromAcct, VD2.AcctNum ToAcct,
        PH.WNum, PH.AcctNum, PH.CNum, A.AcctName
FROM
        A INNER JOIN  PH
        ON A.CNum = PH.CNum AND A.NNum = PH.NNum INNER
JOIN VD as
         VD1 ON VD1.WNum = PH.WNum AND
        A.AcctNum = VD1.AcctNum INNER JOIN VD as VD2 ON
VD2.WNum = PH.WNum
WHERE   VD1.VNum = 1 AND VD2.VNum = 2 AND PH.ProcessID
= "A"

UNION

SELECT    
        VD2.AcctNum FromAcct, VD1.AcctNum ToAcct,
        PH.WNum, PH.AccNum, PH.CNum, A.AcctName
FROM
        A INNER JOIN PH on
        A.CNum = PH.CNum AND
        A.NoteNum_10 = PH.NoteNum_10 INNER JOIN VD as VD1
        ON      VD1.WNum = PH.WNum INNER JOIN   VD as VD2
        ON VD2.WNum = PH.WNum AND A.AcctNum = VD2.AcctNum
WHERE
        VD1.VNum = 1 AND VD2.VNum = 2  AND PH.ProcessID
= "A"

Primary key of VD: Vnum and WNum
Primary key of PH: WNum
        Foreign key with table A: CNum, NNum, AcctNum
Primary key of A: AcctID ( not exist in the query)

My difficulties are:
1. I need to list FromAcct and ToAcct which come from the
same field (AcctNum) of the same table ( VD)
2. Expected result is to list FromAcct, ToAcct where
VD.VNum equal either 1 or 2
For FromAcct, need to join VD, A and PH tables and VD.VNum
should be either 1 or 2

The reason need to join A table is I need to get AcctName
The reason need to join PH table is I need to get CNum

This is not a straightforward query. There is some tricks.
Unfortunately,  For security issue, I can't give query's
scenario.

Thanks for the help.

 
 
 

sql query (UNION) help!!! Thanks

Post by BP Margoli » Sun, 30 Mar 2003 05:07:45


Diane,

The best way to request assistance with an SQL query is by posting (simplified) table schemas (CREATE TABLEs), sample data (INSERTs), and the output you want using the sample data.

Quote:> Unfortunately,  For security issue, I can't give query's
> scenario.

No one is requesting that you post any confidential information. Feel free to create totally phony column names and complete bogus sample data ... but it is a whole lot easier to code a query when table schemas and sample data are posted. Posting a query that isn't what you want is of minimal value in explaining what you do want.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> I have following query which use UNION but I need to
> create a query which return the same result but without
> UNION. Please Help. Thanks!!!

> SELECT  
> VD1.AcctNum FromAcct, VD2.AcctNum ToAcct,
> PH.WNum, PH.AcctNum, PH.CNum, A.AcctName
> FROM
> A INNER JOIN  PH
> ON A.CNum = PH.CNum AND A.NNum = PH.NNum INNER
> JOIN VD as
> VD1 ON VD1.WNum = PH.WNum AND
> A.AcctNum = VD1.AcctNum INNER JOIN VD as VD2 ON
> VD2.WNum = PH.WNum
> WHERE  VD1.VNum = 1 AND VD2.VNum = 2 AND PH.ProcessID
> = "A"

> UNION

> SELECT  
> VD2.AcctNum FromAcct, VD1.AcctNum ToAcct,
> PH.WNum, PH.AccNum, PH.CNum, A.AcctName
> FROM
> A INNER JOIN PH on
> A.CNum = PH.CNum AND
> A.NoteNum_10 = PH.NoteNum_10 INNER JOIN VD as VD1
> ON VD1.WNum = PH.WNum INNER JOIN   VD as VD2
> ON VD2.WNum = PH.WNum AND A.AcctNum = VD2.AcctNum
> WHERE
> VD1.VNum = 1 AND VD2.VNum = 2  AND PH.ProcessID
> = "A"

> Primary key of VD: Vnum and WNum
> Primary key of PH: WNum
> Foreign key with table A: CNum, NNum, AcctNum
> Primary key of A: AcctID ( not exist in the query)

> My difficulties are:
> 1. I need to list FromAcct and ToAcct which come from the
> same field (AcctNum) of the same table ( VD)
> 2. Expected result is to list FromAcct, ToAcct where
> VD.VNum equal either 1 or 2
> For FromAcct, need to join VD, A and PH tables and VD.VNum
> should be either 1 or 2

> The reason need to join A table is I need to get AcctName
> The reason need to join PH table is I need to get CNum

> This is not a straightforward query. There is some tricks.
> Unfortunately,  For security issue, I can't give query's
> scenario.

> Thanks for the help.


 
 
 

sql query (UNION) help!!! Thanks

Post by Dian » Tue, 01 Apr 2003 18:37:59


The expected result is
FromAcct    ToAcct      WNum        AcctNum    
CNum        AcctName            
----------- ----------- ----------- ----------- -----------
 --------------------
12345       12345       1           12345      
1           test1
12345       12345       2           12345      
1           test1
12346       12346       3           12345      
3           test2

Here is script to create test tables/keys and test data.

CREATE TABLE [dbo].[A] (
        [AcctId] [int] IDENTITY (1, 1) NOT NULL ,
        [AcctNum] [char] (11) NOT NULL ,
        [NNum] [char] (5) NULL ,
        [CNum] [int] NOT NULL ,
        [AcctName] [varchar] (20) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PH] (
        [WNum] [int] IDENTITY (1, 1) NOT NULL ,
        [CNum] [int] NULL ,
        [AcctNum] [char] (11) NULL ,
        [NNum] [char] (5) NULL ,
        [ProcessId] [char] (4) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VD] (
        [VNum] [tinyint] NOT NULL ,
        [WNum] [int] NOT NULL ,
        [AcctNum] [char] (11) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[A] WITH NOCHECK ADD
        CONSTRAINT [PK__A__6B1AC8E1] PRIMARY KEY  
NONCLUSTERED
        (
                [AcctId]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[VD] WITH NOCHECK ADD
        CONSTRAINT [PK_VD] PRIMARY KEY  NONCLUSTERED
        (
                [VNum],
                [WNum]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[PH] WITH NOCHECK ADD
        CONSTRAINT [PK__PH__216BEC9A] PRIMARY KEY  
CLUSTERED
        (
                [WNum]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

Insert A Values('12345',       '1',     1,   'test1')
Insert A Values('12345',       '2',     1,   'test1')
Insert A Values('12346',       '1',     2,   'test2')
Insert A Values('12346',       '2',     2,   'test2')
Insert A Values('12345',       '1',     3,   'test1')
Insert A Values('12346',       '1',     3,   'test2')
Insert A Values('12345',       '2',     1,   'test1')
Insert A Values('12347',       '2',     3,   'test3')

Insert PH Values(1, '12345',       '1',     'A')
Insert PH Values(1, '12345',       '2',     'A')
Insert PH Values(3, '12345',       '1',     'A')
Insert PH Values(2, '12346',       '1',     'A')
Insert PH Values(1, '12346',       '2',     'A')
Insert PH Values(3, '12346',       '1',     'A')
Insert PH Values(3, '12347',       '1',     'A')

Insert VD Values(1, 1,   '12345')
Insert VD Values(2, 2,   '12345')
Insert VD Values(1, 3,   '12346')
Insert VD Values(2, 1,   '12345')
Insert VD Values(1, 2,   '12345')
Insert VD Values(1, 7,   '12347')
Insert VD Values(2, 3,   '12346')

Thanks for the help!

 
 
 

sql query (UNION) help!!! Thanks

Post by Ron Talmag » Tue, 01 Apr 2003 21:32:47


Diane,

I tried on this but it's not clear to me where the FromAcct and ToAcct are
coming from.

Ron
--
Ron Talmage
SQL Server MVP


Quote:> The expected result is
> FromAcct    ToAcct      WNum        AcctNum
> CNum        AcctName
> ----------- ----------- ----------- ----------- -----------
>  --------------------
> 12345       12345       1           12345
> 1           test1
> 12345       12345       2           12345
> 1           test1
> 12346       12346       3           12345
> 3           test2

> Here is script to create test tables/keys and test data.

> CREATE TABLE [dbo].[A] (
> [AcctId] [int] IDENTITY (1, 1) NOT NULL ,
> [AcctNum] [char] (11) NOT NULL ,
> [NNum] [char] (5) NULL ,
> [CNum] [int] NOT NULL ,
> [AcctName] [varchar] (20) NULL
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[PH] (
> [WNum] [int] IDENTITY (1, 1) NOT NULL ,
> [CNum] [int] NULL ,
> [AcctNum] [char] (11) NULL ,
> [NNum] [char] (5) NULL ,
> [ProcessId] [char] (4) NULL
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[VD] (
> [VNum] [tinyint] NOT NULL ,
> [WNum] [int] NOT NULL ,
> [AcctNum] [char] (11) NOT NULL
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[A] WITH NOCHECK ADD
> CONSTRAINT [PK__A__6B1AC8E1] PRIMARY KEY
> NONCLUSTERED
> (
> [AcctId]
> ) WITH  FILLFACTOR = 90  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[VD] WITH NOCHECK ADD
> CONSTRAINT [PK_VD] PRIMARY KEY  NONCLUSTERED
> (
> [VNum],
> [WNum]
> ) WITH  FILLFACTOR = 90  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[PH] WITH NOCHECK ADD
> CONSTRAINT [PK__PH__216BEC9A] PRIMARY KEY
> CLUSTERED
> (
> [WNum]
> ) WITH  FILLFACTOR = 90  ON [PRIMARY]
> GO

> Insert A Values('12345',       '1',     1,   'test1')
> Insert A Values('12345',       '2',     1,   'test1')
> Insert A Values('12346',       '1',     2,   'test2')
> Insert A Values('12346',       '2',     2,   'test2')
> Insert A Values('12345',       '1',     3,   'test1')
> Insert A Values('12346',       '1',     3,   'test2')
> Insert A Values('12345',       '2',     1,   'test1')
> Insert A Values('12347',       '2',     3,   'test3')

> Insert PH Values(1, '12345',       '1',     'A')
> Insert PH Values(1, '12345',       '2',     'A')
> Insert PH Values(3, '12345',       '1',     'A')
> Insert PH Values(2, '12346',       '1',     'A')
> Insert PH Values(1, '12346',       '2',     'A')
> Insert PH Values(3, '12346',       '1',     'A')
> Insert PH Values(3, '12347',       '1',     'A')

> Insert VD Values(1, 1,   '12345')
> Insert VD Values(2, 2,   '12345')
> Insert VD Values(1, 3,   '12346')
> Insert VD Values(2, 1,   '12345')
> Insert VD Values(1, 2,   '12345')
> Insert VD Values(1, 7,   '12347')
> Insert VD Values(2, 3,   '12346')

> Thanks for the help!