Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SELECT DISTINCT from two tables

Reply
Thread Tools

SELECT DISTINCT from two tables

 
 
Marco Alting
Guest
Posts: n/a
 
      07-30-2003
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!


 
Reply With Quote
 
 
 
 
Bingo
Guest
Posts: n/a
 
      07-30-2003
> 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


 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      07-30-2003
> 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


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      07-30-2003
Marco Alting wrote:
> 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


 
Reply With Quote
 
Phill. W
Guest
Posts: n/a
 
      07-31-2003
"Marco Alting" <(E-Mail Removed)> wrote in message
news:y8MVa.748343$(E-Mail Removed) ...
.. . .
> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
XPATH equivalent of SELECT DISTINCT andrewmrichards XML 2 12-03-2010 01:42 PM
number of distinct elements in a const container and iterating over the distinct elements Hicham Mouline C++ 1 04-11-2010 10:56 AM
subject: xpath select distinct over 2 elements will XML 1 08-15-2007 10:32 AM
Distinct and Top in Select mianiro ASP General 1 01-02-2007 03:38 PM
Select Distinct DataGrid =?Utf-8?B?YmVub2l0?= ASP .Net 4 10-14-2005 11:49 AM



Advertisments