Help with Join

  • Thread starter Stuart E. Wugalter
  • Start date
S

Stuart E. Wugalter

I have two tables that I want to conditionally merge into a query. The
following code produces what I believe is called a "Cartesian Join." Instead
of only one record per SNPID, giving me about 84 records, it gives me about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6 AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND ((SEQUENOME.SNPID)=[Enter an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
 
K

Ken Snell

Try this:

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6 AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];
 
S

Stuart E. Wugalter

Ken: This worked great. Thank you very much.

Now, I have a slightly different query I want to do. Let's say I have two
tables that look like this:

Table A has fields: id order genename
Tabel B has fields: id order# x y

I want to add columns x and y to Table A where there is a match on id and
order#. I want to keep records in Table A even if they don't have a match in
Table B

Thanks in Advance, Stuart


Ken Snell said:
Try this:

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6 AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];


--
Ken Snell
<MS ACCESS MVP>

Stuart E. Wugalter said:
I have two tables that I want to conditionally merge into a query. The
following code produces what I believe is called a "Cartesian Join." Instead
of only one record per SNPID, giving me about 84 records, it gives me about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6 AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND ((SEQUENOME.SNPID)=[Enter an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
Click to expand...
Click to expand...
 
K

Ken Snell

Try this:

SELECT A.*, B.x, B.y
FROM [Table A] AS A
LEFT JOIN [Table B] AS B
ON A.id = B.id AND A.order = B.[order#];

--
Ken Snell
<MS ACCESS MVP>


Stuart E. Wugalter said:
Ken: This worked great. Thank you very much.

Now, I have a slightly different query I want to do. Let's say I have two
tables that look like this:

Table A has fields: id order genename
Tabel B has fields: id order# x y

I want to add columns x and y to Table A where there is a match on id and
order#. I want to keep records in Table A even if they don't have a match in
Table B

Thanks in Advance, Stuart


Ken Snell said:
Try this:

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6 AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];


--
Ken Snell
<MS ACCESS MVP>

Stuart E. Wugalter said:
I have two tables that I want to conditionally merge into a query. The
following code produces what I believe is called a "Cartesian Join." Instead
of only one record per SNPID, giving me about 84 records, it gives me about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Click to expand...
Click to expand...
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND ((SEQUENOME.SNPID)=[Enter an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
Click to expand...
Click to expand...
Click to expand...
 
S

Stuart E. Wugalter

Ken:

I was not clear when I said "add columns." I actually want to add these new
columns to Table A. I created two fields in Table A that are to be filled in
from Table B. I was trying the following without success:

UPDATE tblMasterTable AS A
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer]
FROM [tbl ABI Lab Info] AS B
WHERE (A.SNPID=B.SNPID) AND (A.ordernum=B.[order #]);

TIA Stuart


Ken Snell said:
Try this:

SELECT A.*, B.x, B.y
FROM [Table A] AS A
LEFT JOIN [Table B] AS B
ON A.id = B.id AND A.order = B.[order#];

--
Ken Snell
<MS ACCESS MVP>


Stuart E. Wugalter said:
Ken: This worked great. Thank you very much.

Now, I have a slightly different query I want to do. Let's say I have two
tables that look like this:

Table A has fields: id order genename
Tabel B has fields: id order# x y

I want to add columns x and y to Table A where there is a match on id and
order#. I want to keep records in Table A even if they don't have a
Click to expand...
match
in
Table B

Thanks in Advance, Stuart
Click to expand...
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];


--
Ken Snell
<MS ACCESS MVP>

I have two tables that I want to conditionally merge into a query. The
following code produces what I believe is called a "Cartesian Join."
Instead
of only one record per SNPID, giving me about 84 records, it gives me
about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Click to expand...
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND
Click to expand...
Click to expand...
((SEQUENOME.SNPID)=[Enter
an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell

Try this:

UPDATE tblMasterTable AS A
LEFT JOIN [tbl ABI Lab Info] AS B
ON A.SNPID=B.SNPID AND A.ordernum=B.[order #]
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer];


--
Ken Snell
<MS ACCESS MVP>


Stuart E. Wugalter said:
Ken:

I was not clear when I said "add columns." I actually want to add these new
columns to Table A. I created two fields in Table A that are to be filled in
from Table B. I was trying the following without success:

UPDATE tblMasterTable AS A
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer]
FROM [tbl ABI Lab Info] AS B
WHERE (A.SNPID=B.SNPID) AND (A.ordernum=B.[order #]);

TIA Stuart


Ken Snell said:
Try this:

SELECT A.*, B.x, B.y
FROM [Table A] AS A
LEFT JOIN [Table B] AS B
ON A.id = B.id AND A.order = B.[order#];

--
Ken Snell
<MS ACCESS MVP>


Stuart E. Wugalter said:
Ken: This worked great. Thank you very much.

Now, I have a slightly different query I want to do. Let's say I have two
tables that look like this:

Table A has fields: id order genename
Tabel B has fields: id order# x y

I want to add columns x and y to Table A where there is a match on id and
order#. I want to keep records in Table A even if they don't have a
Click to expand...
match
in
Table B

Thanks in Advance, Stuart


Try this:

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Click to expand...
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];


--
Ken Snell
<MS ACCESS MVP>

I have two tables that I want to conditionally merge into a query. The
following code produces what I believe is called a "Cartesian Join."
Instead
of only one record per SNPID, giving me about 84 records, it gives me
about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND
Click to expand...
((SEQUENOME.SNPID)=[Enter
an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,

The left join is suspicious. You would replace that data in the left
table with NULL just because there is no match in the right table? even more
if the updated columns are new ones, so probably filled with NULL already...
Don't you intend a RIGHT JOIN instead? it is more "standard", with Jet,
since it has, as effect, to add the new rows in the left table (in the
unpreserved table while you update that unpreserved table)... and that will
be it with a RIGHT JOIN.

I don't say the LEFT join is wrong, I just find it suspicious, even if it
may have its uses.


Vanderghast, Access MVP


Ken Snell said:
Try this:

UPDATE tblMasterTable AS A
LEFT JOIN [tbl ABI Lab Info] AS B
ON A.SNPID=B.SNPID AND A.ordernum=B.[order #]
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer];


--
Ken Snell
<MS ACCESS MVP>


Stuart E. Wugalter said:
Ken:

I was not clear when I said "add columns." I actually want to add these new
columns to Table A. I created two fields in Table A that are to be
Click to expand...
filled
in
from Table B. I was trying the following without success:

UPDATE tblMasterTable AS A
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer]
FROM [tbl ABI Lab Info] AS B
WHERE (A.SNPID=B.SNPID) AND (A.ordernum=B.[order #]);

TIA Stuart


Ken Snell said:
Try this:

SELECT A.*, B.x, B.y
FROM [Table A] AS A
LEFT JOIN [Table B] AS B
ON A.id = B.id AND A.order = B.[order#];

--
Ken Snell
<MS ACCESS MVP>


Ken: This worked great. Thank you very much.

Now, I have a slightly different query I want to do. Let's say I
Click to expand...
have
two
tables that look like this:

Table A has fields: id order genename
Tabel B has fields: id order# x y

I want to add columns x and y to Table A where there is a match on
Click to expand...
id
and
order#. I want to keep records in Table A even if they don't have a match
in
Table B

Thanks in Advance, Stuart


Try this:

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID, Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];


--
Ken Snell
<MS ACCESS MVP>

I have two tables that I want to conditionally merge into a
Click to expand...
query.
The
following code produces what I believe is called a "Cartesian Join."
Instead
of only one record per SNPID, giving me about 84 records, it
Click to expand...
gives
me
about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND ((SEQUENOME.SNPID)=[Enter
an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell

I believe you're right, Michel. Thanks!

--
Ken Snell
<MS ACCESS MVP>

Michel Walsh said:
Hi,

The left join is suspicious. You would replace that data in the left
table with NULL just because there is no match in the right table? even more
if the updated columns are new ones, so probably filled with NULL already...
Don't you intend a RIGHT JOIN instead? it is more "standard", with Jet,
since it has, as effect, to add the new rows in the left table (in the
unpreserved table while you update that unpreserved table)... and that will
be it with a RIGHT JOIN.

I don't say the LEFT join is wrong, I just find it suspicious, even if it
may have its uses.


Vanderghast, Access MVP


Ken Snell said:
Try this:

UPDATE tblMasterTable AS A
LEFT JOIN [tbl ABI Lab Info] AS B
ON A.SNPID=B.SNPID AND A.ordernum=B.[order #]
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer];


--
Ken Snell
<MS ACCESS MVP>


Stuart E. Wugalter said:
Ken:

I was not clear when I said "add columns." I actually want to add
Click to expand...
these
new
columns to Table A. I created two fields in Table A that are to be
Click to expand...
filled
in
from Table B. I was trying the following without success:

UPDATE tblMasterTable AS A
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer]
FROM [tbl ABI Lab Info] AS B
WHERE (A.SNPID=B.SNPID) AND (A.ordernum=B.[order #]);

TIA Stuart


Try this:

SELECT A.*, B.x, B.y
FROM [Table A] AS A
LEFT JOIN [Table B] AS B
ON A.id = B.id AND A.order = B.[order#];

--
Ken Snell
<MS ACCESS MVP>


Ken: This worked great. Thank you very much.

Now, I have a slightly different query I want to do. Let's say I have
two
tables that look like this:

Table A has fields: id order genename
Tabel B has fields: id order# x y

I want to add columns x and y to Table A where there is a match on id
and
order#. I want to keep records in Table A even if they don't have a
match
in
Table B

Thanks in Advance, Stuart


Try this:

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];


--
Ken Snell
<MS ACCESS MVP>

I have two tables that I want to conditionally merge into a query.
The
following code produces what I believe is called a "Cartesian Join."
Instead
of only one record per SNPID, giving me about 84 records, it gives
me
about
30k+ records. TIA

Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
[email protected]

SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND
((SEQUENOME.SNPID)=[Enter
an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
Click to expand...
Click to expand...
Click to expand...
 
Top