SELECT DISTINCT from two tables

[Marco Alting]

Hi

I have two table which are related:

table1 holds personellinformation
table2 holds nodeInformation

The nodes in table2 can have a nodeOwner which will then get a recordID from
table1. A person can own multiple nodes.

Now I want to display all nodeowners, but not the duplicates (if they own
multiple nodes).

What would be the SQL syntax for that, using ASP with VB?

I've tried using DISTINCT, but I do also need the NodeID which is always
unique so I still get all duplicates.


Hope you can help!

 

[Bingo]

I've tried using DISTINCT, but I do also need the NodeID which is always

unique so I still get all duplicates.

If you need the nodeID, how could you not get duplicates for a person owning
multiple node?
How could the select statement now which nodeID you want to select?

Bingo

 

[Aaron Bertrand - MVP]

Now I want to display all nodeowners, but not the duplicates (if they own

multiple nodes).

I've tried using DISTINCT, but I do also need the NodeID which is always
unique so I still get all duplicates.

Your requirements conflict with each other. Please give us meaningful
requirements (e.g. actual table structure - including keys and datatypes,
sample data, and desired results).

http://www.aspfaq.com/5006

 

[Bob Barrows]

Hi

I have two table which are related:

table1 holds personellinformation
table2 holds nodeInformation

The nodes in table2 can have a nodeOwner which will then get a
recordID from table1. A person can own multiple nodes.

Now I want to display all nodeowners, but not the duplicates (if they
own multiple nodes).

What would be the SQL syntax for that, using ASP with VB?

I've tried using DISTINCT, but I do also need the NodeID which is
always unique so I still get all duplicates.


Hope you can help!

It sounds as if you need to group by node id instead of using distinct, but
I can't be sure without seeing sample data and desired results.

Bob Barrows

 

[Phill. W]

.. . .

Now I want to display all nodeowners, but not the duplicates (if
they own multiple nodes).

You SQL for this one should look something like this :

Select Distinct NodeOwner
From table1 t1
, table2 t2
where t1.[ownerID?] = t2.nodeOwner

That will get you all the people that own Nodes, regardless of
how many.

I've tried using DISTINCT, but I do also need the NodeID which
is always unique so I still get all duplicates.

Now you have a problem. If you want Owner /and/ Node, you're
going to get duplication; there's no way around that. If you wanted,
say, a Grouped List of Owners and their Nodes, you'd need
something like this

Select Distinct NodeOwner
, NodeID
From table1 t1
, table2 t2
where t1.[ownerID?] = t2.nodeOwner
Order By t1.[ownerID?]
, t2.nodeID

Then

rsData.Open( [SQL], oDBConnection )

Do While Not rsData.EOF

sCurrentOwner = rsData( "[ownerID?]" ).Value

' [Start] Display Owner

Do While Not rsData.EOF
' Bit of a kludge since VBScript doesn't short-circuit If's
If sCurrentOwner <> rsData( "[ownerID?]" ).Value Then
Exit Do
End If

' Display Node

rsData.MoveNext
Loop

' [End] Display Owner (if, say, you're using HTML tables)

Loop

HTH,
Phill W.