August 29, 2008 at 8:51 am
To my understanding, an INNER JOIN is symmetric, it should not make any difference which order the tables are specified.
Yesterday one of the people from the marketing department asked why her query was not working. Rows that should be there did not show up. I started to play with it, removing extraneous material, down to the bare bones and still am getting nonsensical (to me) results. The original was in sql2000, but I imported the tables into 2005 and am having similar problems.
there are two tables, and a simple INNER JOIN. Why do the two queries produce markedly different results?
--first query
SELECT agrt778.prd, itdmn78.prodno
FROM agrt778 INNER JOIN
itdmn78 ON agrt778.prd = itdmn78.prodno
WHERE (agrt778.prd = N'240526-004')
--Produces one record (correct)
--5120 rows when where clause removed
--- second query
SELECT itdmn78.prodno, agrt778.prd
FROM itdmn78 INNER JOIN
agrt778 ON itdmn78.PRDNO = agrt778.prd
WHERE (agrt778.prd = N'240526-004')
--produces zero records (wrong???)
-- 76 rows when where clause removed
...
-- FORTRAN manual for Xerox Computers --
August 29, 2008 at 9:00 am
Are you able to re-create this scenario with data in temporary tables. It would be cool to get to the bottom of this. Please post an example for us to play with.
August 29, 2008 at 9:03 am
jay holovacs (8/29/2008)
FROM agrt778 INNER JOINitdmn78 ON agrt778.prd = itdmn78.prodno
FROM itdmn78 INNER JOIN
agrt778 ON itdmn78.PRDNO = agrt778.prd
It looks like you're joining on two different columns in the itdmn78 table, the first has prodno and the second has prdno
August 29, 2008 at 9:03 am
Wow, no idea what would be wrong. I've never seen the order of tables matter, and I'm not sure why you get different results.
Are these in the same SSMS window? I almost feel like you're hitting two different databases.
August 29, 2008 at 9:05 am
HI there,
I'm not sure if it's a typeO or not but you ON conditions are not the same
ON agrt778.prd = itdmn78.prodno
ON itdmn78.PRDNO = agrt778.prd
Please could you also supply us with some code for creating some sample data.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 29, 2008 at 9:07 am
You have in the first query:
ON agrt778.prd = itdmn78.prodno
and in the second query:
ON itdmn78.PRDNO = agrt778.prd
notice PRDNO vs. prodno
Is that a typo in posting to this forum? Or is it the cause of the problem?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 29, 2008 at 9:12 am
holycow
This data came from old AS400 tables with 8 character field names. They did some really bizarre things with naming conventions. Yes it looks like neither myself or the other user noticed the typo.
thanx
...
-- FORTRAN manual for Xerox Computers --
August 29, 2008 at 9:12 am
Doh! Eyes getting old. Can't believe I missed that.
August 29, 2008 at 9:20 am
My eyes are getting old too.
The query has to produce the same results when it's reversed, so what else could it be?
Had the case been the same in both queries I might have missed too. When I saw the different case I figured the query had been retyped, not done the lazy, oops, I mean smart way - copy/paste.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply