[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Syntax to find subset plus mainlist

Posted on 2014-08-21
7
Medium Priority
?
118 Views
Last Modified: 2014-08-21
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'
0
Comment
Question by:rwheeler23
  • 3
  • 3
7 Comments
 
LVL 14

Expert Comment

by:Russell Fox
ID: 40277186
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40277414
...
               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
 

Author Comment

by:rwheeler23
ID: 40277479
This was the fix. Thanks.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Closing Comment

by:rwheeler23
ID: 40277480
Thank you.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40277483
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40277486
You're welcome!  And sorry about the copy/paste error!
0
 

Author Comment

by:rwheeler23
ID: 40277512
No apologies necessary. Thanks for taking the time to help with something should have seen myself. You always provide top shelf responses.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question