Need INTERSECT not JOIN

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

It is my understanding that these are similar but I cannot seem to make the
INNER JOIN work as I would expect. When I do the inner join fields add added
to the result and it seems that all combinations are the result, not the
intersection. Both tables are the same structure and I want the resulting
table to be the exact same structure and the records to be the same but only
those that appear in both tables.

Does this make sense? some help would be greatly appreciated!
 
Sounds like you are not joining on all the fields you expect the query to
match on. Can you post the SQL to explain further?
 
SELECT * FROM 68_PriceAnalysis INNER JOIN 69_PriceAnalysis ON
[68_PriceAnalysis].[NNS MATID (PART NO)] LIKE [69_PriceAnalysis].[NNS MATID
(PART NO)];

This seems to join the 2 tables left to right (ie. twice as many fields as
one of the originals) and seems to give every unique combination (i.e. the
result is about 20x the number of records compared to the sum of count for
each). I simply want a resulting table as if I had appended one with the
other but only showing records with IDs that appear in both tables.

Thanks!
 
To do what you want, you need:

SELECT DISTINCT
pa1.*
FROM
68_PriceAnalysis pa1
INNER JOIN
69_PriceAnalysis pa2 ON
pa1.[NNS MATID (PART NO)] = pa2.[NNS MATID (PART NO)]
UNION
SELECT DISTINCT
pa1.*
FROM
69_PriceAnalysis pa1
INNER JOIN
68_PriceAnalysis pa2 ON
pa1.[NNS MATID (PART NO)] = pa2.[NNS MATID (PART NO)]


A union appends the results of 2 or more queries (removing duplicates -
"UNION ALL" would leave the duplicates). You can see I'm taking the UNION
of 2 SELECT queries. The first returns all records and fields from
68_PriceAnalysis where there's a matching record in 69_PriceAnalysis. The
second returns all records and fields from 69_PriceAnalysis where there's a
matching record in 68_PriceAnalysis.

Unfortunately, you have to write UNION query SQL by hand - you can't do it
in the visual query designer.
 
Try replacing LIKE with =

--
HTH
Van T. Dinh
MVP (Access)


Mike said:
SELECT * FROM 68_PriceAnalysis INNER JOIN 69_PriceAnalysis ON
[68_PriceAnalysis].[NNS MATID (PART NO)] LIKE [69_PriceAnalysis].[NNS MATID
(PART NO)];

This seems to join the 2 tables left to right (ie. twice as many fields as
one of the originals) and seems to give every unique combination (i.e. the
result is about 20x the number of records compared to the sum of count for
each). I simply want a resulting table as if I had appended one with the
other but only showing records with IDs that appear in both tables.

Thanks!

JohnFol said:
Sounds like you are not joining on all the fields you expect the query to
match on. Can you post the SQL to explain further?
Click to expand...
Click to expand...
 
Not sure why that should matter, Van, since he doesn't have any wildcard
characters in the query. If [NNS MATID (PART NO)] is not unique in the
tables, then he's going to get a cartesian product regardless.

The "twice as many fields" makes sense, since he's using SELECT *.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Van T. Dinh said:
Try replacing LIKE with =

--
HTH
Van T. Dinh
MVP (Access)


Mike said:
SELECT * FROM 68_PriceAnalysis INNER JOIN 69_PriceAnalysis ON
[68_PriceAnalysis].[NNS MATID (PART NO)] LIKE [69_PriceAnalysis].[NNS MATID
(PART NO)];

This seems to join the 2 tables left to right (ie. twice as many fields
as
one of the originals) and seems to give every unique combination (i.e.
the
result is about 20x the number of records compared to the sum of count
for
each). I simply want a resulting table as if I had appended one with the
other but only showing records with IDs that appear in both tables.

Thanks!

JohnFol said:
Sounds like you are not joining on all the fields you expect the query to
match on. Can you post the SQL to explain further?


It is my understanding that these are similar but I cannot seem to make
the
INNER JOIN work as I would expect. When I do the inner join fields add
added
to the result and it seems that all combinations are the result, not the
intersection. Both tables are the same structure and I want the resulting
table to be the exact same structure and the records to be the same but
only
those that appear in both tables.

Does this make sense? some help would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Hi Doug

No, I didn't think "=" would be the solution but "Like" looks too weird
without wildcards.

Yes, the * without the Table qualifier will give all the Fields in the 2
Tables involved in the join.
 
Hi,

That would delete the duplicated records initially present: if you
start with (a, 1) (a, 1) (b 2) and "intersect" with itself, you would
get only two records (while someone could expect to retrieve the three
initial records). I propose:

myTableA INTER myTableB ::

SELECT a.*
FROM myTableA As a
INNER JOIN ( SELECT DISTINCT g1, g2, g3 FROM myTableB ) AS b
ON a.f1=b.g1 AND a.f2=b.g2 AND a.f3=b.g3


should do the job. I assumed 3 fields involved in the "comparison".


On the other hand, note that A inter B is not the same as B inter A.
With

A = { (a, 1), (a, 1), (b,2) }

and with

B = { (a, 1), (b, 2) }


then

A inter B returns A,

while

B inter A returns B


Only if there is no duplicated "tuple", it is that INTERSECTION, defined as
in the supplied SQL statement, does behave with a property of commutatively
( A inter B = B inter A ).

Also note that if a NULL is implied in a comparison, the record with the
implied won't be kept by the intersection as defined here up.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


.... but that is not updateable. Another approach is to use a simple IN.

If the there just one field implied in the comparison:

SELECT *
FROM myTableA
WHERE f1 IN( SELECT g1 FROM myTable2)



If there are more than one field, use the 'standard' conversion from IN to
EXISTS:


SELECT *
FROM myTable As A
WHERE EXISTS( SELECT *
FROM myTableB as b
WHERE a.f1=b.g1 AND a.f2=b.g2 AND
a.f3=b.g3)




Hoping it may help,
Vanderghast, Access MVP
 
Maybe I misunderstood the question, but I think Mike's initial complaint was
that he was doing something like,

SELECT * FROM table1 INNER JOIN table2 ON table1.key = table2.key

This was giving him a result set where each row had all of the fields of
table 1 and table 2.

What he said he wanted to do was to basically append the rows from the 2
tables into one result set having the same record structure as one of the
tables. (Both source tables have the same structure). But, he also wanted
only rows with id's that could be found in both tables.

I probably should have used a UNION ALL instead of UNION, but I think he
does need a UNION query of some sort.

Your queries will just give him a subset of the rows from "table a". But I
think Mike needs rows from "table a" AND "table b" - when those rows contain
an ID field value that's present in both tables.
 
Hi,


I see... that is another way to see the original question, indeed... I
was mind-geared toward the "set" operation called INTERSECTION, with
records NOT having (implying) a (the) primary key, so records may have
duplicated "values" used in the implied comparison(s). A lightly different
perception of the OP. :-)

Vanderghast, Access MVP
 
Back
Top