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
October 21, 2020 at 9:02 am
Thanks lot now i got expected result
October 21, 2020 at 9:37 am
What solution did you go for?
I want to be the very best
Like no one ever was
October 21, 2020 at 10:55 am
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
October 26, 2020 at 4:48 pm
>> 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply