SQL JOIN for Unmatched records & JOIN fields from another table

[Hugh self taught]

Hi Learned people,

I have the following 2 SQL codes

SELECT Couples_2009.CoupleID, Couples_2009.CoupleNumber,
Couples_2009.MaleID, Couples_2009.FemaleID, Couples_2009.PermNumberCollect
FROM Couples_2009 LEFT JOIN Couples ON Couples_2009.[MaleID] =
Couples.[MaleID]
WHERE (((Couples.MaleID) Is Null));

For a result of unmatched records &

SELECT Couples.CoupleID, Couples.CoupleNumber, [Male].[Surname]+'
'+[Male].[First_Name] AS Male, [Female].[Surname]+' '+[Female].[First_Name]
AS Female
FROM Competitors AS Male INNER JOIN (Couples INNER JOIN Competitors AS
Female ON Couples.FemaleID = Female.Competitor_Idx) ON Male.Competitor_Idx =
Couples.MaleID
ORDER BY Val(Couples.CoupleNumber), [Male].[Surname]+' '+[Male].[First_Name];


I need to combine these so that my "unmatched records" query gives me the
names instead of the FK/PK reference.

I can read & understand each of them but can't put them together.

Many thanks for any help.
H

 

[vanderghast]

The first query returns records in Couples_2009 NOT IN table Couples. You
can't get anymore information from data from Couples since there is no match
in Couples. So you have to continue with table (query) Couple_2009.


To extract other information from other tables, add the other required
tables with an outer join involving records from Couples_2009 (and keeping
all the records from Couples_2009 and only those matching from the added
tables). You should be able to use the graphical query designer for that
purpose.



Vanderghast, Access MVP

 

[Hugh self taught]

Hi Vanderghast,

I've tried unsuccessfully to do that hence my post. I either get a datasheet
of no records or when I get the records they still don't reflect the names.

The first query returns records in Couples_2009 NOT IN table Couples. You
can't get anymore information from data from Couples since there is no match
in Couples. So you have to continue with table (query) Couple_2009.


To extract other information from other tables, add the other required
tables with an outer join involving records from Couples_2009 (and keeping
all the records from Couples_2009 and only those matching from the added
tables). You should be able to use the graphical query designer for that
purpose.



Vanderghast, Access MVP

Hi Learned people,

I have the following 2 SQL codes

SELECT Couples_2009.CoupleID, Couples_2009.CoupleNumber,
Couples_2009.MaleID, Couples_2009.FemaleID, Couples_2009.PermNumberCollect
FROM Couples_2009 LEFT JOIN Couples ON Couples_2009.[MaleID] =
Couples.[MaleID]
WHERE (((Couples.MaleID) Is Null));

For a result of unmatched records &

SELECT Couples.CoupleID, Couples.CoupleNumber, [Male].[Surname]+'
'+[Male].[First_Name] AS Male, [Female].[Surname]+'
'+[Female].[First_Name]
AS Female
FROM Competitors AS Male INNER JOIN (Couples INNER JOIN Competitors AS
Female ON Couples.FemaleID = Female.Competitor_Idx) ON Male.Competitor_Idx
=
Couples.MaleID
ORDER BY Val(Couples.CoupleNumber), [Male].[Surname]+'
'+[Male].[First_Name];


I need to combine these so that my "unmatched records" query gives me the
names instead of the FK/PK reference.

I can read & understand each of them but can't put them together.

Many thanks for any help.
H

 

[vanderghast]

If you get NULL data, it MAY be because you use Couples rather than
Couples_2009 (as you did in the second part of your first post), which is
wrong, since anything from Couples, in the first query, will be null and
would find no match.

You cannot get NOT record if you effectively use OUTER JOIN with keeping all
records from Couple_2009 option, for EACH and EVERY join (unless your WHERE
clause involves more than what you posted in the first query of your first
post)... or unless Couple_2009 has not a single record to start with.

Be sure you use outer joins only (they will have one arrow, in the graphical
designer) and that the arrow is always AWAY from table Couples_2009. Be sure
you don't use implicit inner join in the where clause (ie, don't involve a
criteria between two tables through the WHERE clause), do it with a join,
and an outer join that is. Don't, DON'T have something in the WHERE clause
that says something like: ... AND Couples_2009.someField =
Couples.someField AND ...



Vanderghast, Access MVP

Hi Vanderghast,

I've tried unsuccessfully to do that hence my post. I either get a
datasheet
of no records or when I get the records they still don't reflect the
names.

The first query returns records in Couples_2009 NOT IN table Couples. You
can't get anymore information from data from Couples since there is no
match
in Couples. So you have to continue with table (query) Couple_2009.


To extract other information from other tables, add the other required
tables with an outer join involving records from Couples_2009 (and
keeping
all the records from Couples_2009 and only those matching from the added
tables). You should be able to use the graphical query designer for that
purpose.



Vanderghast, Access MVP

Hi Learned people,

I have the following 2 SQL codes

SELECT Couples_2009.CoupleID, Couples_2009.CoupleNumber,
Couples_2009.MaleID, Couples_2009.FemaleID,
Couples_2009.PermNumberCollect
FROM Couples_2009 LEFT JOIN Couples ON Couples_2009.[MaleID] =
Couples.[MaleID]
WHERE (((Couples.MaleID) Is Null));

For a result of unmatched records &

SELECT Couples.CoupleID, Couples.CoupleNumber, [Male].[Surname]+'
'+[Male].[First_Name] AS Male, [Female].[Surname]+'
'+[Female].[First_Name]
AS Female
FROM Competitors AS Male INNER JOIN (Couples INNER JOIN Competitors AS
Female ON Couples.FemaleID = Female.Competitor_Idx) ON
Male.Competitor_Idx
=
Couples.MaleID
ORDER BY Val(Couples.CoupleNumber), [Male].[Surname]+'
'+[Male].[First_Name];


I need to combine these so that my "unmatched records" query gives me
the
names instead of the FK/PK reference.

I can read & understand each of them but can't put them together.

Many thanks for any help.
H

 

[Hugh self taught]

Hi there,

I eventually got to the following code which gives me the exact result I'm
looking for.

Thanks for your help.

SELECT Couples_2009.CoupleID, Couples_2009.CoupleNumber,
[Male].[First_Name]+' '+[Male].[Surname] AS Male, [Female].[First_Name]+'
'+[Female].[Surname] AS Female, Couples_2009.PermNumberCollect
FROM (Competitors AS Male INNER JOIN (Couples_2009 INNER JOIN Competitors AS
Female ON Couples_2009.FemaleID = Female.Competitor_Idx) ON
Male.Competitor_Idx = Couples_2009.MaleID) LEFT JOIN Couples ON
Couples_2009.MaleID = Couples.MaleID
WHERE (((Couples.MaleID) Is Null));

If you get NULL data, it MAY be because you use Couples rather than
Couples_2009 (as you did in the second part of your first post), which is
wrong, since anything from Couples, in the first query, will be null and
would find no match.

You cannot get NOT record if you effectively use OUTER JOIN with keeping all
records from Couple_2009 option, for EACH and EVERY join (unless your WHERE
clause involves more than what you posted in the first query of your first
post)... or unless Couple_2009 has not a single record to start with.

Be sure you use outer joins only (they will have one arrow, in the graphical
designer) and that the arrow is always AWAY from table Couples_2009. Be sure
you don't use implicit inner join in the where clause (ie, don't involve a
criteria between two tables through the WHERE clause), do it with a join,
and an outer join that is. Don't, DON'T have something in the WHERE clause
that says something like: ... AND Couples_2009.someField =
Couples.someField AND ...



Vanderghast, Access MVP

Hi Vanderghast,

I've tried unsuccessfully to do that hence my post. I either get a
datasheet
of no records or when I get the records they still don't reflect the
names.

The first query returns records in Couples_2009 NOT IN table Couples. You
can't get anymore information from data from Couples since there is no
match
in Couples. So you have to continue with table (query) Couple_2009.


To extract other information from other tables, add the other required
tables with an outer join involving records from Couples_2009 (and
keeping
all the records from Couples_2009 and only those matching from the added
tables). You should be able to use the graphical query designer for that
purpose.



Vanderghast, Access MVP



message Hi Learned people,

I have the following 2 SQL codes

SELECT Couples_2009.CoupleID, Couples_2009.CoupleNumber,
Couples_2009.MaleID, Couples_2009.FemaleID,
Couples_2009.PermNumberCollect
FROM Couples_2009 LEFT JOIN Couples ON Couples_2009.[MaleID] =
Couples.[MaleID]
WHERE (((Couples.MaleID) Is Null));

For a result of unmatched records &

SELECT Couples.CoupleID, Couples.CoupleNumber, [Male].[Surname]+'
'+[Male].[First_Name] AS Male, [Female].[Surname]+'
'+[Female].[First_Name]
AS Female
FROM Competitors AS Male INNER JOIN (Couples INNER JOIN Competitors AS
Female ON Couples.FemaleID = Female.Competitor_Idx) ON
Male.Competitor_Idx
=
Couples.MaleID
ORDER BY Val(Couples.CoupleNumber), [Male].[Surname]+'
'+[Male].[First_Name];


I need to combine these so that my "unmatched records" query gives me
the
names instead of the FK/PK reference.

I can read & understand each of them but can't put them together.

Many thanks for any help.
H