SQL Query Syntax to find subset plus mainlist

I have this query that works fine for one report but now the users want to turn it around. What this query does is find all parts from the primary vendor that are also offered from other vendors. Now what the user want is a list of all parts from the primary regardless of whether they come from another vendor plus to also list those parts that come from other vendors. In one example the current query returns 3,500 records however, there are 30,000 parts that come from this vendor. I need to include the other 26,500 parts.

Sample Data:
ITEMNMBR,VENDORID,LSTORDDT,LSORDQTY,LASTCOST
WIDGET1,VEND1,10/31/13,4,3.0
WIDGET2,VEND1,12/31/13,6,4.0
WIDGET2,VEND2,01/10/14,5,3.5

So in this case, they want to see both WIDGET1 and WIDGET2 where for WIDGET1 there are no other vendors but for WIDGET2 there is another vendor. Currently, my script will only list WIDGET2.

SELECT     V.ITEMNMBR,
                  V.VENDORID AS PRMVENDOR,
                  V.LSTORDDT AS PRMLSTORDDT,
                  V.LSORDQTY AS PRMLSORDQTY,
                  V.LAST_Originating_Cost AS PRMLASTCOST,
                  OV.VENDORID AS ALTVENDOR,
                 OV.LSTORDDT AS ALTLSTORDDT,
                 OV.LSORDQTY AS ALTLSORDQTY,
                 OV.LAST_Originating_Cost AS ALTLASTCOST
FROM      IV00103 V
                 LEFT OUTER JOIN IV00103 OV
                        ON V.ITEMNMBR = OV.ITEMNMBR
                        AND V.VENDORID <> OV.VENDORID
WHERE    V.VENDORID = @VENDORID AND V.LSTORDDT>'01/01/1901' AND OV.VENDORID IS NOT NULL AND OV.LSTORDDT>'01/01/1901'
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
change your LEFT OUTER JOIN  to a LEFT JOIN - does that give you what you expect?
SELECT
	V.ITEMNMBR,
	V.VENDORID AS PRMVENDOR,
	V.LSTORDDT AS PRMLSTORDDT,
	V.LSORDQTY AS PRMLSORDQTY,
	V.LAST_Originating_Cost AS PRMLASTCOST,
	OV.VENDORID AS ALTVENDOR,
	OV.LSTORDDT AS ALTLSTORDDT,
	OV.LSORDQTY AS ALTLSORDQTY,
	OV.LAST_Originating_Cost AS ALTLASTCOST
FROM IV00103 V
	LEFT JOIN IV00103 OV
		ON V.ITEMNMBR = OV.ITEMNMBR
		AND V.VENDORID <> OV.VENDORID
WHERE V.VENDORID = @VENDORID 
	AND V.LSTORDDT>'01/01/1901' 
	AND OV.VENDORID IS NOT NULL 
	AND OV.LSTORDDT>'01/01/1901'

Open in new window

0
Scott PletcherSenior DBACommented:
...
               LEFT OUTER JOIN IV00103 OV
                         ON V.ITEMNMBR = OV.ITEMNMBR
                         AND V.VENDORID <> OV.VENDORID
                         AND V.LSTORDDT>'01/01/1901'
WHERE    V.VENDORID = @VENDORID AND V.LSTORDDT>'01/01/1901'  --don't compare any "OV." columns here
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
This was the fix. Thanks.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

rwheeler23Author Commented:
Thank you.
0
Scott PletcherSenior DBACommented:
D'OH, CORRECTION:

...
                LEFT OUTER JOIN IV00103 OV
                          ON V.ITEMNMBR = OV.ITEMNMBR
                          AND V.VENDORID <> OV.VENDORID
                          AND OV.LSTORDDT>'01/01/1901'
0
Scott PletcherSenior DBACommented:
You're welcome!  And sorry about the copy/paste error!
0
rwheeler23Author Commented:
No apologies necessary. Thanks for taking the time to help with something should have seen myself. You always provide top shelf responses.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.