Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002

    Unanswered: DISTINCT From two (or more) tables

    Do not know if this is possible without creating temp tables or new tables, but basically I have a few tables that have similar fields

    Table 1: lname
    Table 2: lname
    Table 3: lname

    The tables are not the same, for instance all lname in T1 are not in T2, etc.

    I want to be able to retrieve all the Distinct emails from the tables

    For one table Select Distinct lname From t1...after that I am stumped on how to return just one column from all three tables...the lname is indexed so I am trying to avoid temp or new tables so I can then do quicker lookups to other fields other than from tables 1 through 3 ...

    I guess I could do three seperate selects and place results in an array, but...

    FWIW these items were all non-related to being with for the most part

  2. #2
    Join Date
    Mar 2002
    If the tables have the same structure use three selects with UNION between, and perhaps a hardcoded field that tells you from which table the field comes.

    SELECT DISTINCT email, '1' as tablenr FROM table1
    SELECT DISTINCT email, '2' as tablenr FROM table2
    SELECT DISTINCT email, '3' as tablenr FROM table3

  3. #3
    Join Date
    Apr 2002

    DISTINCT is entire row

    According to me the DISTINCT must be true for the entire result row,
    so there could be identical adresses returned with different hardcoded values (1,2,3). Drop the hardcoded value and you are OK.

    Greetings, Edwin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts