CODEWITHSUNDEEP
sqlms-access
Shawn
Shawn

Reputation: 11391

Why does adding a concatenation break this MS ACCESS "SQL" query?

Here are the table involved

O (for origin): | PText | CText |
P (products):     | id | PText | ... |
C (category):     | id | CText | .... |
D (destination):  | pid | cid |

What I need to do is to find a product ids using O's PText and category ids using O's CTexts, and put these ids into D

This query does exactly that:

INSERT INTO D ( pid, cid )
SELECT P.id, C.id
FROM (O INNER JOIN P ON O.PText = P.PText) INNER JOIN C ON C.CText = O.CText
WHERE P.id IS NOT NULL AND C.id IS NOT NULL AND P.id NOT IN (SELECT pid FROM D);

The problem is that O has a few mistakes. All the PTexts are missing a "0" in front (Microsoft decided to take it away when taking data from an Access table and putting it in a Excel spreadsheet). Therefore, the PText doesn't match, but "0"+PText does match.

Here is my second attempt:

INSERT INTO D ( pid, cid )
SELECT P.id, C.id
FROM (O INNER JOIN P ON **"0" &** O.PText = P.PText) INNER JOIN C ON C.CText = O.CText
WHERE P.id IS NOT NULL AND C.id IS NOT NULL AND P.id NOT IN (SELECT pid FROM D);

This second query doesn't work. It says You are about to update 0 row(s). My Question: How can the second query not work when the first one does?

Note: this is using MS ACCESS 2002

Upvotes: 1

Views: 477

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50980

Both queries are probably working. The second one simply isn't finding any rows to UPDATE. The reason is likely that when you massage the O.TEXT field by adding an initial "0" character to it then the INNER JOIN fails to match any records in M.

Try the query:

 SELECT M.id, C.id FROM (O INNER JOIN M ON "0" & O.PText = M.PText)

Does it return records? If not, that's your problem.

Try this:

 SELECT M.PText, C.PText FROM (O INNER JOIN M ON O.PText = M.PText)

What gets returned? (I'm not sure I kept up with the changes you made to the column names, you may need to modify them before running the query).

Upvotes: 3

madC
madC

Reputation: 243

Number is probably a number field, thats why the leading zero is stript. You concat will make it a "text" field and you can't join text vs number column.

Upvotes: 0

Related Questions