[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

Sql database question need to pull data from both tables that doesn't exist in the other

The result I am looking for is to select Distinct product_no from two Price_Lists.  This is only producing product_no from one price_list.  Can you help?

SELECT DISTINCT Accessories.Product_no AS [Part Number], Accessories.Product_desc_txt AS [Accessory Description], PRICE_PROD.UOM_CD AS UOM,  PRICE_PROD.PRODUCT_PRC AS Price, Accessories.Oracle_Product_no, Accessories.Main_txt, Accessories.Sub_txt, Accessories.Third_txt, PRICE_PROD.Price_List_No FROM Accessories INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO WHERE ((PRICE_PROD.Price_List_No = 300) AND Not Exists(SELECT Accessories.Product_no From Accessories INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO  Where PRICE_PROD.Price_List_No = 234)) OR PRICE_PROD.Price_List_No =234 ORDER BY Accessories.Product_no
0
sjmeiners
Asked:
sjmeiners
  • 6
  • 4
1 Solution
 
ksaulCommented:
If you can do without the Price_List_No field in the result set you can get a distinct list like this:
SELECT DISTINCT
      Accessories.Product_no AS [Part Number],
      Accessories.Product_desc_txt AS [Accessory Description],
      PRICE_PROD.UOM_CD AS UOM,  
      PRICE_PROD.PRODUCT_PRC AS Price,
      Accessories.Oracle_Product_no,
      Accessories.Main_txt, Accessories.Sub_txt,
      Accessories.Third_txt,
FROM Accessories
INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO
WHERE PRICE_PROD.Price_List_No  IN (300, 234)

If you have to have the Price_List_No field try a union query like this
SELECT Accessories.Product_no AS [Part Number],
      Accessories.Product_desc_txt AS [Accessory Description],
      PRICE_PROD.UOM_CD AS UOM,  
      PRICE_PROD.PRODUCT_PRC AS Price,
      Accessories.Oracle_Product_no,
      Accessories.Main_txt, Accessories.Sub_txt,
      Accessories.Third_txt, PRICE_PROD.Price_List_No
FROM Accessories
INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO
WHERE ((PRICE_PROD.Price_List_No = 234)
UNION
SELECT Accessories.Product_no AS [Part Number],
      Accessories.Product_desc_txt AS [Accessory Description],
      PRICE_PROD.UOM_CD AS UOM,  
      PRICE_PROD.PRODUCT_PRC AS Price,
      Accessories.Oracle_Product_no,
      Accessories.Main_txt, Accessories.Sub_txt,
      Accessories.Third_txt, PRICE_PROD.Price_List_No
FROM Accessories
INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO
WHERE PRICE_PROD.Price_List_No = 300
AND Not Exists(
      SELECT Accessories.Product_no
      From Accessories
      INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO  
      Where PRICE_PROD.Price_List_No = 234)
ORDER BY Accessories.Product_no
0
 
sjmeinersAuthor Commented:
I do need the price list number so I tried the second solution and got this error.
Error in WHERE clause near 'UNION'.
Unable to parse query text.
0
 
JimBrandleyCommented:
The error in ksaul's select is in the unnecessary and unbalanced parentheses in the first WHERE clause. Change
 WHERE ((PRICE_PROD.Price_List_No = 234)
to
WHERE PRICE_PROD.Price_List_No = 234

And the syntax error will be gone.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JimBrandleyCommented:
I have been having difficulty understanding exactly what you are after, but I think this might give you the result you want:

SELECT DISTINCT Accessories.Product_no AS [Part Number],
                Accessories.Product_desc_txt AS [Accessory Description],
                PRICE_PROD.UOM_CD AS UOM,  PRICE_PROD.PRODUCT_PRC AS Price,
                Accessories.Oracle_Product_no, Accessories.Main_txt,
                Accessories.Sub_txt, Accessories.Third_txt, PRICE_PROD.Price_List_No
  FROM Accessories FULL OUTER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO
 WHERE (PRICE_PROD.Price_List_No = 300
   AND Not Exists(SELECT Accessories.Product_no
                    From Accessories INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO
                   Where PRICE_PROD.Price_List_No = 234))
    OR PRICE_PROD.Price_List_No =234
ORDER BY Accessories.Product_no
0
 
sjmeinersAuthor Commented:
Thanks to all.  I still can't get it to work.  Jim I tried to both and neither worked.  What I am trying to accomplish is there are two different price lists in one table with part numbers that may be in both price lists.  I want the result to be one distinct list created from both price lists.  So each part will show once in this combined list.  Does that help?
0
 
JimBrandleyCommented:
That helps. I think the only way to accomplish that is to:
Select Accessories.Product_no AS [Part Number],
          Accessories.Product_desc_txt AS [Accessory Description],
          Accessories.Oracle_Product_no, Accessories.Main_txt,
          Accessories.Sub_txt, Accessories.Third_txt,
          PRICE_PROD1.UOM_CD AS UOM1,  PRICE_PROD1.PRODUCT_PRC AS Price1,
          PRICE_PROD1.Price_List_No1,
          PRICE_PROD2.UOM_CD AS UOM2,  PRICE_PROD2.PRODUCT_PRC AS Price2,
          PRICE_PROD2.Price_List_No2
FROM - etc.
Will that work for you?

Jim

0
 
sjmeinersAuthor Commented:
Could you finish the code that you think it should be?  Sorry I am being brain dead
0
 
JimBrandleyCommented:
I got brain dead last night too. Here it is with the etc filled in:
SELECT DISTINCT a.Product_no AS [Part Number],
                a.Product_desc_txt AS [Accessory Description],
                a.Oracle_Product_no, a.Main_txt,
                a.Sub_txt, a.Third_txt,
                (SELECT Price_List_No FROM PRICE_PROD WHERE a.Product_no = PRICE_PROD.PRODUCT_NO AND PRICE_PROD.Price_List_No = 300) AS [Price_List_No1],
                (SELECT UOM_CD FROM PRICE_PROD WHERE a.Product_no = PRICE_PROD.PRODUCT_NO AND PRICE_PROD.Price_List_No = 300) AS [UOM1],
                (SELECT PRODUCT_PRC FROM PRICE_PROD WHERE a.Product_no = PRICE_PROD.PRODUCT_NO AND PRICE_PROD.Price_List_No = 300) AS [Price1],
                (SELECT Price_List_No FROM PRICE_PROD WHERE a.Product_no = PRICE_PROD.PRODUCT_NO AND PRICE_PROD.Price_List_No = 234) AS [Price_List_No2],
                (SELECT UOM_CD FROM PRICE_PROD WHERE a.Product_no = PRICE_PROD.PRODUCT_NO AND PRICE_PROD.Price_List_No = 234) AS [UOM2],
                (SELECT PRODUCT_PRC FROM PRICE_PROD WHERE a.Product_no = PRICE_PROD.PRODUCT_NO AND PRICE_PROD.Price_List_No = 234) AS [Price2]
  FROM Accessories a INNER JOIN PRICE_PROD ON Accessories.Product_no = PRICE_PROD.PRODUCT_NO
 WHERE PRICE_PROD.Price_List_No = 300 OR PRICE_PROD.Price_List_No = 234
ORDER BY Accessories.Product_no

Jim
0
 
sjmeinersAuthor Commented:
Here is the error I am getting.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Accessories'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PRICE_PROD'.
0
 
JimBrandleyCommented:
That's from your original select. Is the table name price_list?
0
 
JimBrandleyCommented:
If so, here's the updated select:
SELECT DISTINCT a.Product_no AS [Part Number],
                a.Product_desc_txt AS [Accessory Description],
                a.Oracle_Product_no, a.Main_txt,
                a.Sub_txt, a.Third_txt,
                (SELECT Price_List_No FROM price_list WHERE a.Product_no = price_list.PRODUCT_NO AND price_list.Price_List_No = 300) AS [Price_List_No1],
                (SELECT UOM_CD FROM price_list WHERE a.Product_no = price_list.PRODUCT_NO AND price_list.Price_List_No = 300) AS [UOM1],
                (SELECT PRODUCT_PRC FROM price_list WHERE a.Product_no = price_list.PRODUCT_NO AND price_list.Price_List_No = 300) AS [Price1],
                (SELECT Price_List_No FROM price_list WHERE a.Product_no = price_list.PRODUCT_NO AND price_list.Price_List_No = 234) AS [Price_List_No2],
                (SELECT UOM_CD FROM price_list WHERE a.Product_no = price_list.PRODUCT_NO AND price_list.Price_List_No = 234) AS [UOM2],
                (SELECT PRODUCT_PRC FROM price_list WHERE a.Product_no = price_list.PRODUCT_NO AND price_list.Price_List_No = 234) AS [Price2]
  FROM Accessories a INNER JOIN price_list ON Accessories.Product_no = price_list.PRODUCT_NO
 WHERE price_list.Price_List_No = 300 OR price_list.Price_List_No = 234
ORDER BY Accessories.Product_no
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now