Hi,

I'd like to know if this query below is feasible in one single SQL
statement. If not, I'll have to merge 2 sets of table and I'd like to
avoid this if possible.

Specs:
---------
Table VPN_A:
AutoId (int, auto inc)
NodeName (Text)

Table VPN_B:
AutoID (int, auto inc)
NodeName (Text)

Table Nodes_A:
AutoID (int, auto inc)
ParentID (int, FK: VPN_A.AutoID)
NodeDesc (Text)

Table Nodes_B:
AutoID (int, auto inc)
ParentID (int, FK: VPN_B.AutoID)
NodeDesc (Text)

I'd like to have all distinct records from Nodes_A and Nodes_B Where

Nodes_A.Parent_ID = VPN_A.AutoID and
Nodes_B.Parent_ID = VPN_B.AutoID

But so far I'm getting lots of duplicates, even when I try a GROUP BY.

Thanks for your help.

For starters -- display what the source data looks like and what you
want to the result dataset to look like:

table: VPN_A table: VPN_B
AutoID NodeName AutoID NodeName
1 a1 1 b1
2 a2 2 b2
...


table: Nodes_A table: Nodes_B
AutoID ParentID NodeDesc AutoID ParentID NodeDesc
...


Result Dataset
--


Rich

*** Sent via Developersdex http://www.developersdex.com ***

circuit_breaker (AT) canada (DOT) com wrote:
Are you using a UNION query? If so, are you using UNION or UNION ALL?
Try UNION.

Then again, you'd want to get a distinct list from each query you union
of the dupes aren't in each table...IOW a record exist in both A and B.

Sorry, It goes like this:

table: VPN_A table: VPN_B
AutoID NodeName AutoID NodeName
101 VPNA101 201 VPNB201
102 VPNA102 202 VPNB202
103 VPNA103 203 VPNB203

table: Nodes_A table: Nodes_B
AutoID ParentID NodeDesc AudoID ParentID NodeDesc
1001 101 PC_1001 2001 203 PC_2001
1002 103 PC_1002 2002 201 PC_2002
1003 103 PC_1003 2003 202 PC_2003


ResultSet I'd like to get:

NodeID NodeDesc NodeName
1001 PC_1001 VPNA101
1002 PC_1002 VPNA102
1003 PC_1003 VPNA103
2001 PC_2001 VPNA201
2002 PC_2002 VPNA202
2003 PC_2003 VPNA203

Is it possible?
Thanks

On Jan 26, 11:09*am, Rich P <rpng... (AT) aol (DOT) com> wrote:

Here are the tables specs.

table: VPN_A table: VPN_B
AutoID NodeName AutoID NodeName
101 VPNA101 201 VPNB201
102 VPNA102 202 VPNB202
103 VPNA103 203 VPNB203


table: Nodes_A table: Nodes_B
AutoID ParentID NodeDesc AudoID ParentID NodeDesc
1001 101 PC_1001 2001 203 PC_2001
1002 103 PC_1002 2002 201 PC_2002
1003 103 PC_1003 2003 202 PC_2003


ResultSet I'd like to get:


NodeID NodeDesc NodeName
1001 PC_1001 VPNA101
1002 PC_1002 VPNA103
1003 PC_1003 VPNA103
2001 PC_2001 VPNA203
2002 PC_2002 VPNA201
2003 PC_2003 VPNA202


Can it be done?
Thanks


On Jan 26, 11:09*am, Rich P <rpng... (AT) aol (DOT) com> wrote:

You could try something like this:

select t2.AutoID As NodeID, t2.NodeDesc, t1.NodeName
From VPN_A t1 Join Nodes_A t2 On t1.AutoID = t2.ParentID
Union All
select t2.AutoID As NodeID, t2.NodeDesc, t1.NodeName
From VPN_B t1 Join Nodes_B t2 On t1.AutoID = t2.ParentID


Rich

*** Sent via Developersdex http://www.developersdex.com ***