SELECT DISTINCT from two tables

Discussion in 'ASP General' started by Marco Alting, Jul 30, 2003.

  1. Marco Alting

    Marco Alting Guest

    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!
    Marco Alting, Jul 30, 2003
    #1
    1. Advertising

  2. Marco Alting

    Bingo Guest

    > 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
    Bingo, Jul 30, 2003
    #2
    1. Advertising

  3. > 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
    Aaron Bertrand - MVP, Jul 30, 2003
    #3
  4. Marco Alting

    Bob Barrows Guest

    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
    Bob Barrows, Jul 31, 2003
    #4
  5. Marco Alting

    Phill. W Guest

    "Marco Alting" <> wrote in message
    news:y8MVa.748343$...
    .. . .
    > 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.
    Phill. W, Jul 31, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?YmVub2l0?=

    Select Distinct DataGrid

    =?Utf-8?B?YmVub2l0?=, Oct 14, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    2,133
    =?Utf-8?B?YmVub2l0?=
    Oct 14, 2005
  2. will
    Replies:
    1
    Views:
    1,143
    Pavel Lepin
    Aug 15, 2007
  3. Hicham Mouline
    Replies:
    1
    Views:
    390
    Kai-Uwe Bux
    Apr 11, 2010
  4. andrewmrichards

    XPATH equivalent of SELECT DISTINCT

    andrewmrichards, Dec 2, 2010, in forum: XML
    Replies:
    2
    Views:
    3,410
    Martin Honnen
    Dec 3, 2010
  5. mianiro

    Distinct and Top in Select

    mianiro, Jan 2, 2007, in forum: ASP General
    Replies:
    1
    Views:
    147
    Bob Barrows [MVP]
    Jan 2, 2007
Loading...

Share This Page