Help with an Inner Join?

G

Grahammer

I'm trying to get my head around an Inner Join query and it's not working...

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblCategory)) ON
tblCategory.CategoryID = Cat;

The Union works... The following query returns a single column named Cat
that holds all the Categories found in the three columns of the tblCategory
table (Category, Category2 and Category3).

SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION SELECT
Cat FROM ( SELECT Category3 AS Cat FROM tblCategory);

Can someone lend a pair of eyes and let me know what I'm doing wrong?
 
L

Lynn Trapp

Well, the first thing that popped out at me is the line

tblCategory.CategoryID = Cat

What is "Cat"? Are you hoping for that to be a parameter that the user
enters or is it a text value in the table tblCategory? If the first then you
need to wrap it in square brackets -- [Cat]. If the second, then you need
to wrap it in quotation marks -- "Cat".
 
G

Grahammer

Cat is the column returned by the Union:

SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION SELECT
Cat FROM ( SELECT Category3 AS Cat FROM tblCategory);

....results in a table with a single column named Cat .

I'm trying to join the resulting table above to my table named tblCategory.
I want the CategoryID and Category columns from tblCategory for each of the
rows returned by my Union query.


Lynn Trapp said:
Well, the first thing that popped out at me is the line

tblCategory.CategoryID = Cat

What is "Cat"? Are you hoping for that to be a parameter that the user
enters or is it a text value in the table tblCategory? If the first then you
need to wrap it in square brackets -- [Cat]. If the second, then you need
to wrap it in quotation marks -- "Cat".

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Grahammer said:
I'm trying to get my head around an Inner Join query and it's not working...

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblCategory)) ON
tblCategory.CategoryID = Cat;

The Union works... The following query returns a single column named Cat
that holds all the Categories found in the three columns of the tblCategory
table (Category, Category2 and Category3).

SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION SELECT
Cat FROM ( SELECT Category3 AS Cat FROM tblCategory);

Can someone lend a pair of eyes and let me know what I'm doing wrong?
Click to expand...
Click to expand...
 
L

Lynn Trapp

I'm with you now. I think your query may be confused as to what is being
referenced. Let's give this a try:

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblCategory)) As A ON
tblCategory.CategoryID = A.Cat;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Grahammer said:
Cat is the column returned by the Union:

SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION SELECT
Cat FROM ( SELECT Category3 AS Cat FROM tblCategory);

...results in a table with a single column named Cat .

I'm trying to join the resulting table above to my table named tblCategory.
I want the CategoryID and Category columns from tblCategory for each of the
rows returned by my Union query.


Lynn Trapp said:
Well, the first thing that popped out at me is the line

tblCategory.CategoryID = Cat

What is "Cat"? Are you hoping for that to be a parameter that the user
enters or is it a text value in the table tblCategory? If the first then you
need to wrap it in square brackets -- [Cat]. If the second, then you need
to wrap it in quotation marks -- "Cat".

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Grahammer said:
I'm trying to get my head around an Inner Join query and it's not working...

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory
Click to expand...
INNER
JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblCategory)) ON
tblCategory.CategoryID = Cat;

The Union works... The following query returns a single column named Cat
that holds all the Categories found in the three columns of the tblCategory
table (Category, Category2 and Category3).

SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION SELECT
Cat FROM ( SELECT Category3 AS Cat FROM tblCategory);

Can someone lend a pair of eyes and let me know what I'm doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

PMFBI

I could be wrong...but I think that when
it comes to using subqueries in a FROM
clause in Access, there is a gap in what
Access will let you write and what it will
try to change it to when it compiles the SQL.

It will accept parentheses as you type it in
the query designer, but after you save and
reopen it, it will change the subquery to the
"brackets/period" form which will not allow
any further brackets within it.

I might suggest:


SELECT DISTINCT
C.CategoryID,
C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat
FROM tblCategory
UNION
SELECT Category2
FROM tblCategory
UNION
SELECT Category3
FROM tblCategory]. AS q
ON C.Category=q.Cat;

Apologies again for butting in,
especially if I am wrong.

Good luck,

Gary Walter
 
G

Grahammer

I have to apologize... I just noticed an error in the SQL statement I
included originally...

There are TWO tables involved. I've corrected the SQL below.

SELECT DISTINCT C.CategoryID, C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat FROM tblEntries
UNION SELECT Category2 FROM tblEntries
UNION SELECT Category3 FROM tblEntries].
AS q
ON C.Category=q.Cat;

Looks good, but haven't had a chance to test it out.

Thanks all!
Gary Walter said:
PMFBI

I could be wrong...but I think that when
it comes to using subqueries in a FROM
clause in Access, there is a gap in what
Access will let you write and what it will
try to change it to when it compiles the SQL.

It will accept parentheses as you type it in
the query designer, but after you save and
reopen it, it will change the subquery to the
"brackets/period" form which will not allow
any further brackets within it.

I might suggest:


SELECT DISTINCT
C.CategoryID,
C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat
FROM tblCategory
UNION
SELECT Category2
FROM tblCategory
UNION
SELECT Category3
FROM tblCategory]. AS q
ON C.Category=q.Cat;

Apologies again for butting in,
especially if I am wrong.

Good luck,

Gary Walter

I'm with you now. I think your query may be confused as to what is being
referenced. Let's give this a try:

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblCategory)) As A ON
tblCategory.CategoryID = A.Cat;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Click to expand...
Click to expand...
 
L

Lynn Trapp

Let us know how it turns out.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Grahammer said:
I have to apologize... I just noticed an error in the SQL statement I
included originally...

There are TWO tables involved. I've corrected the SQL below.

SELECT DISTINCT C.CategoryID, C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat FROM tblEntries
UNION SELECT Category2 FROM tblEntries
UNION SELECT Category3 FROM tblEntries].
AS q
ON C.Category=q.Cat;

Looks good, but haven't had a chance to test it out.

Thanks all!
Gary Walter said:
PMFBI

I could be wrong...but I think that when
it comes to using subqueries in a FROM
clause in Access, there is a gap in what
Access will let you write and what it will
try to change it to when it compiles the SQL.

It will accept parentheses as you type it in
the query designer, but after you save and
reopen it, it will change the subquery to the
"brackets/period" form which will not allow
any further brackets within it.

I might suggest:


SELECT DISTINCT
C.CategoryID,
C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat
FROM tblCategory
UNION
SELECT Category2
FROM tblCategory
UNION
SELECT Category3
FROM tblCategory]. AS q
ON C.Category=q.Cat;

Apologies again for butting in,
especially if I am wrong.

Good luck,

Gary Walter

I'm with you now. I think your query may be confused as to what is being
referenced. Let's give this a try:

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory
Click to expand...
Click to expand...
INNER
JOIN
Click to expand...
Click to expand...
 
G

Grahammer

Ended up with:

SELECT DISTINCT A.CategoryID, A.Category
FROM tblCategory AS A INNER JOIN tblEntries AS B
ON A.CategoryID IN (B.Category, B.Category2, B.Category3);


Lynn Trapp said:
Let us know how it turns out.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Grahammer said:
I have to apologize... I just noticed an error in the SQL statement I
included originally...

There are TWO tables involved. I've corrected the SQL below.

SELECT DISTINCT C.CategoryID, C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat FROM tblEntries
UNION SELECT Category2 FROM tblEntries
UNION SELECT Category3 FROM tblEntries].
AS q
ON C.Category=q.Cat;

Looks good, but haven't had a chance to test it out.

Thanks all!
Gary Walter said:
PMFBI

I could be wrong...but I think that when
it comes to using subqueries in a FROM
clause in Access, there is a gap in what
Access will let you write and what it will
try to change it to when it compiles the SQL.

It will accept parentheses as you type it in
the query designer, but after you save and
reopen it, it will change the subquery to the
"brackets/period" form which will not allow
any further brackets within it.

I might suggest:


SELECT DISTINCT
C.CategoryID,
C.Category
FROM tblCategory AS C
INNER JOIN
[SELECT Category As Cat
FROM tblCategory
UNION
SELECT Category2
FROM tblCategory
UNION
SELECT Category3
FROM tblCategory]. AS q
ON C.Category=q.Cat;

Apologies again for butting in,
especially if I am wrong.

Good luck,

Gary Walter

I'm with you now. I think your query may be confused as to what is being
referenced. Let's give this a try:

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory
Click to expand...
INNER
JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblCategory)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblCategory) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblCategory)) As A ON
tblCategory.CategoryID = A.Cat;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union IIF statement 6
Nested JOIN clauses - order of precedence (Access 2007) 2
getting fewer records from a union query than in individual table 5
Cascading lists continued 5
How to apply a conditional Select distinct (or conditional Where onduplicated cell values in a colum 3
Help with SQL Structure 9
multiple queries problem 3
perform INNER JOIN on query results 2

Top