Getting Records not in both tables

I have 2 tables and I want to retrieve all record that are in table 1, but
not in table 2. Here's my data.
Table 1: EstEquipment
Columns: EstEqpPK, EstimatePK, Description
Table 2: CrewEquipment
Columns: CrewEqpPK, EstEqpPK, quantity
I need all records in EstEquipment with a certain EstimatePK that are not in
CrewEquipment. I've tried all sorts of combinations of inner joins, outer
join, and not exists, but I can't seem to get it. This is my first attempt a
t
SQL, so bear with me.
This was my last attempt that got close:
SELECT EstEquipment.* FROM EstEquipment
LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK = CrewEquipment
.EstEqpPK
WHERE EstimatePK = '4947A70B4396448C'
Thanks!Try this one:
SELECT
e.EstEqpPK,
e.EstimatePK,
e.Description
FROM
EstEquipment e
LEFT OUTER JOIN
CrewEquipment c
ON
e.EstEqpPK = c.EstEqpPK
WHERE
e.EstimatePK = '4947A70B4396448C'
AND c.EstEqpPK IS NULL;
"Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
news:CCD86D0D-60BC-4658-9E3A-4F9C49F63592@.microsoft.com...
>I have 2 tables and I want to retrieve all record that are in table 1, but
> not in table 2. Here's my data.
> Table 1: EstEquipment
> Columns: EstEqpPK, EstimatePK, Description
> Table 2: CrewEquipment
> Columns: CrewEqpPK, EstEqpPK, quantity
> I need all records in EstEquipment with a certain EstimatePK that are not
> in
> CrewEquipment. I've tried all sorts of combinations of inner joins, outer
> join, and not exists, but I can't seem to get it. This is my first attempt
> at
> SQL, so bear with me.
> This was my last attempt that got close:
> SELECT EstEquipment.* FROM EstEquipment
> LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK = CrewEquipment
> .EstEqpPK
> WHERE EstimatePK = '4947A70B4396448C'
> Thanks!|||Try
SELECT EstEquipment.* FROM EstEquipment
LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK =
CrewEquipment
.EstEqpPK
WHERE EstimatePK = '4947A70B4396448C'
AND CreqEquipment.EstEqpPK is NULL
HTH,
Stu|||Thanks, that got it. I had tried = NULL, but that didn't return anything. I'
d
better study up on the syntax.
"Stu" wrote:

> Try
> SELECT EstEquipment.* FROM EstEquipment
> LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK =
> CrewEquipment
> ..EstEqpPK
> WHERE EstimatePK = '4947A70B4396448C'
> AND CreqEquipment.EstEqpPK is NULL
> HTH,
> Stu
>|||Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
"Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
news:E8475694-5E80-444B-BFDE-1F938DF76DBC@.microsoft.com...
> Thanks, that got it. I had tried = NULL, but that didn't return anything.
> I'd
> better study up on the syntax.|||I got that from my C background. You use to do this:
IF (x == null)
Thanks for the explanation.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Nothing will ever = NULL, since the definition of NULL is unknown.
> Think about it this way, if I have a form with a field that says "gender"
> and I forget to check either male or female, can you say with any certaint
y
> that I am:
> (a) male?
> (b) female?
> (c) not male?
> (d) not female?
> Further, can you say with any certainty that someone else, who also forgot
> to specify their gender, is:
> (a) the same gender as me?
> (b) the opposite gender from me?
> (c) not the same gender as me?
> (d) not the opposite gender from me?
>
>
> "Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
> news:E8475694-5E80-444B-BFDE-1F938DF76DBC@.microsoft.com...
>
>