#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Taylorsville, Ky
    Posts
    2
    Rep Power
    0

    Problem coding select in multiple tables


    I am working on the following SQL select and I am having a mental block on how to get it fixed. I have two tables that I need to match on the codes in each table. If there is a just one record in Table1 with the same code as one record in table2 and both the date and name match then dont output those two records. Output all records if there are more than 1 record with the same code in each table. Below is some example data that is representive of a sample in the two tables and how the output should look based on that data:

    Table1
    code date name
    aaaa 1/1/2003 billy bob
    bbbb 2/2/2004 louis lewis
    cccc 3/3/2005 joe crab
    dddd 4/4/2006 mary little
    eeee 5/5/2007 joe black


    Table2
    code date name
    aaaa 2/2/2004 larry cole
    aaaa 3/3/2005 nat king
    bbbb 2/2/2004 louis lewis
    cccc 3/3/2005 joe crab
    cccc 6/6/2008 dennis jackson
    dddd 7/7/2009 missy muffet
    dddd 5/5/2007 joe black
    eeee 8/8/2010 elton rocket



    desired output results from select

    aaaa 1/1/2003 billy bob aaaa 2/2/2004 larry cole
    aaaa 1/1/2003 billy bob aaaa 3/3/2005 nat king
    cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab
    cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson
    dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
    dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
    eeee 5/5/2007 joe black eeee 8/8/2010 elton rocket


    Here is the select that I have so far:

    select table1.rowid, table1_code, table1_date, table1_name,
    table2.rowid, table2_code, table2_date, table2_name from table1, table2
    where table1_code= table2_code
    order by table1_code;

    The above select gives me all records just fine, but does not eliminate single records that match. I tried using the Count(table1_code) > 1 and table2 code but I get a message about inproper grouping. Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Taylorsville, Ky
    Posts
    2
    Rep Power
    0
    Ok I have gotten a little further now using this select:

    select table1.rowid, table1_code, table1_date, table1_name,
    table2.rowid, table2_code, table2_date, table2_name
    from table1, table2
    where
    table1_code= table2_code and
    table2_code in (select min(table_code2) from table2
    group by table2_code
    having count(*) > 1)
    order by table1_code;

IMN logo majestic logo threadwatch logo seochat tools logo