query

  • Hi

    i have 3 table that need to find out which id missing two tables

    TABLES: Main,table1 and table2

    compare with Main-table against the table1 and table2 which id is missing that two table show

     

     

     

     

  • Not sure from description what you are trying to accomplish.

    Do you want all IDS from the Main table that are in Table 1 AND in Table2?

    --> Filter from Main if there is no match in T1 AND T2

    select M.ID
    from #Main M
    left join Table1 T1 on T1.ID = M.ID
    left join Table2 T2 on T2.ID = M.ID
    where T1.ID is null
    and T2.ID is null

    Do you want all IDs in the Main table that are in either Table1 or Table2?

    --> Filter Main if there is either Match in T1 or T2

    select M.ID
    from #Main M
    left join Table1 T1 on T1.ID = M.ID
    left join Table2 T2 on T2.ID = M.ID
    where T1.ID is null
    OR T2.ID is null

    I want to be the very best
    Like no one ever was

  • Thanks lot now i got expected result

  • What solution did you go for?

    I want to be the very best
    Like no one ever was

  • below one i got the answer

    select M.ID

    from #Main M

    left join Table1 T1 on T1.ID = M.ID

    left join Table2 T2 on T2.ID = M.ID

    where T1.ID is null

    OR T2.ID is null

  • >> i have 3 table that need to find out which id missing two tables

    TABLES: Main,table1 and table2 <<

    Why did you fail to post any DDL? If you had had a beginning course in basic data modeling, you would know that "_id" is what we call an attribute property in the ISO standards. There is no such thing as a generic, universal "id" in RDBMS. I'm going to assume that, like most people who don't know how to do RDBMS, you intended this column to be the key in all three tables. Do you know how a references clause works?

    CREATE TABLE Mains

    (main_id CHAR(10) NOT NULL PRIMARY KEY,

    ..;

    CREATE TABLE Foobar1

    (main_id CHAR(10) NOT NULL PRIMARY KEY,

    REFERENCES Mains(main_id)

    ON DELETE CASCADE,

    ..);

    CREATE TABLE Foobar2

    (main_id CHAR(10) NOT NULL PRIMARY KEY,

    REFERENCES Mains(main_id)

    ON DELETE CASCADE,,

    ..);

    Let's first write a query that shows which of these main identifiers are common to all three tables:

    SELECT main_id

    FROM

    (SELECT main_id FROM Mains

    INTERSECT

    SELECT main_id FROM Foobar1

    INTERSECT

    SELECT main_id FROM Foobar2) AS X

    you can now remove them from the Mains with an EXCEPT operation.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply