September 8, 2009 at 11:45 am
I have two tables I'm working with. They are Crazy. Their names are [mentor table] and [Mentor with Mentor Address]. I call them MT and MA respectively.
Both tables contain address information. Wierd? Yeah, I'm trying to understand the purpose too.
So I wrote a query i thought would tell me if information in one table was also in another table.
select id, firstname, lastname from [mentor table] where exists (select id, firstname, lastname from [Mentor with Mentor Address])ORDER BY LastName, FirstName, ID
Evidently I don't understand the EXISTS keyword. Everything in MT, the table in the main query, was returned. I thus came to the wrong conclusion that everything in the MT table was also in MA. However, when i tried to join them, i got nothing.
Here is the issue, the resultset had tons of names NOT IN the MA subquery. But EVERYTHING in the MA table WAS indeed in the MT table. I went through name by name. So what i don't understand is why did the above table return names that were not in the subquery?
Also ID appears in both the Main query and the subquery, but they don't MATCH!!! So why did i get a result at all? Why did it ignore the ID column? I would have thought that everything in the row would have to match for it to EXIST in the subquery. FIRSTNAME, LASTNAME, and ID!
Thanks!
September 8, 2009 at 11:58 am
EXISTS evaluates to a boolean value, so true/false, based off of the query inside the EXISTS clause. The way your query is written, you'll get all rows from the Mentor table as long as there is a row in the MA table. The key here is as long as there's a row, there is nothing here to see if the row has matching values.
If you're looking for matches between the 2 tables, I'd use an INNER JOIN.
select m.id,
m.firstname,
m.lastname
from [mentor table] m
INNER JOIN [Mentor with Mentor Address] MA
ON m.id = ma.id AND m.firstname = MA.firstname AND m.lastname = MA.lastname
ORDER BY m.LastName, m.FirstName, m.ID
September 8, 2009 at 12:00 pm
Jacob Pressures (9/8/2009)
I have two tables I'm working with. They are Crazy. Their names are [mentor table] and [Mentor with Mentor Address]. I call them MT and MA respectively.Both tables contain address information. Wierd? Yeah, I'm trying to understand the purpose too.
So I wrote a query i thought would tell me if information in one table was also in another table.
select id, firstname, lastname from [mentor table] where exists (select id, firstname, lastname from [Mentor with Mentor Address])ORDER BY LastName, FirstName, IDEvidently I don't understand the EXISTS keyword. Everything in MT, the table in the main query, was returned. I thus came to the wrong conclusion that everything in the MT table was also in MA. However, when i tried to join them, i got nothing.
Here is the issue, the resultset had tons of names NOT IN the MA subquery. But EVERYTHING in the MA table WAS indeed in the MT table. I went through name by name. So what i don't understand is why did the above table return names that were not in the subquery?
Also ID appears in both the Main query and the subquery, but they don't MATCH!!! So why did i get a result at all? Why did it ignore the ID column? I would have thought that everything in the row would have to match for it to EXIST in the subquery. FIRSTNAME, LASTNAME, and ID!
Thanks!
I think you will need to have a where clause in your exists statement:
select id, firstname, lastname from [mentor table] MT
where exists (select 1 from [Mentor with Mentor Address] MA
where MA.id = MT.id
and MA.firstname = MT.firstName
and MA.lastname = MT.lastname
)ORDER BY LastName, FirstName, ID
OR
select id, firstname, lastname from [mentor table] MT
inner join [Mentor with Mentor Address] MA
on MA.firstname = MT.firstname and MA.lastname = MT.lastname and MT.id = MA.id
This will get you the matches for both first and last name. You could modify it to just get 1 column if needed.
September 8, 2009 at 12:02 pm
EXISTS is true whenever any rows would be returned by the query. You should pair it with a subquery that has a where clause.
IF EXISTS (select 1 from dbo.SomeTable WHERE id = @id )
IF EXISTS (select 1 from dbo.SomeTable WHERE SomeTable.id = SomeOtherTable.id )
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 12:26 pm
Ok guys thanks! That answers my question.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply