SQL QUERY PROBLEM

Question

Post reply

SQL QUERY PROBLEM

biren

Right there with Babe

Points: 719
More actions
May 27, 2010 at 3:44 pm

#219522

I have two tables in which few columns are same as mentioned in my query condition, what so I am to compare two tables and display which does not exists in other column by comparing it.
so, my question is my query correct or incorrect because I get incorrect result when i run in sql management studio, can anybody please guide me on this.
select brand_name,category_id,product_n,color_code_n,color_name,QtyLocation3,QtyLocation11 from och_products where not exists (select * from productscolortb_withsize where productscolortb_withsize.brandid = och_products.category_id and productscolortb_withsize.categoryname = och_products.brand_name and REPLACE(REPLACE(REPLACE(productscolortb_withsize.productname,' ',''),'/',''),'-','') = REPLACE(REPLACE(REPLACE(och_products.product_n,' ',''),'/',''),'-','') and REPLACE(REPLACE(REPLACE(productscolortb_withsize.colorcode,' ',''),'/',''),'-','') = REPLACE(REPLACE(REPLACE(och_products.color_code_n,' ',''),'/',''),'-','')) AND QtyLocation3 > 0 or QtyLocation11 > 0 ORDER BY brand_name,product_n ASC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 1

I guess your OR conditions should be placed inside parenthesis "()"

But I dont understand why are you replacing characters from both left and right side since its same in both side.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Lowell SSC Guru Points: 323518 More actions · Answer 2

i formatted your SQL and converted it to use some CTE's;

look at the final sql and tell us what is not working: how is the data incorrect?

WITH t2 AS

(SELECT

REPLACE(

REPLACE(productscolortb_withsize.productname, ' ', ''), '/', ''), '-', '')

AS productname,

REPLACE(

REPLACE(productscolortb_withsize.colorcode, ' ', ''), '/', ''), '-', '')

AS colorcode,

brandid,

categoryname

FROM productscolortb_withsize ),

t1 AS

(SELECT

REPLACE(

REPLACE(och_products.product_n, ' ', ''), '/', ''), '-', '')

AS product_n,

REPLACE(

REPLACE(och_products.color_code_n, ' ', ''), '/', ''), '-', '')

AS color_code_n,

brand_name,

category_id,

product_n,

color_code_n,

color_name,

QtyLocation3,

QtyLocation11

FROM och_products )

--the basic query, a bit more readable

SELECT

t1.*

FROM t1

left outer join t2

ON t2.brandid = t1.category_id

AND t2.categoryname = t1.brand_name

AND t2.productname = t1.product_n

AND t2.colorcode = t1.color_code_n

WHERE t2. brandid IS NULL

AND (t1.QtyLocation3 > 0

OR t1.QtyLocation11 > 0 )

ORDER BY

t1.brand_name, t1.product_n ASC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

biren Right there with Babe Points: 719 More actions · Answer 3

Hari.Sharma (5/27/2010)
I guess your OR conditions should be placed inside parenthesis "()"
But I dont understand why are you replacing characters from both left and right side since its same in both side.

this worked thanks !!

biren Right there with Babe Points: 719 More actions · Answer 4

Lowell (5/27/2010)
i formatted your SQL and converted it to use some CTE's;
look at the final sql and tell us what is not working: how is the data incorrect?
WITH t2 AS
(SELECT
REPLACE(
REPLACE(
REPLACE(productscolortb_withsize.productname, ' ', ''), '/', ''), '-', '')
AS productname,
REPLACE(
REPLACE(
REPLACE(productscolortb_withsize.colorcode, ' ', ''), '/', ''), '-', '')
AS colorcode,
brandid,
categoryname
FROM productscolortb_withsize ),
t1 AS
(SELECT
REPLACE(
REPLACE(
REPLACE(och_products.product_n, ' ', ''), '/', ''), '-', '')
AS product_n,
REPLACE(
REPLACE(
REPLACE(och_products.color_code_n, ' ', ''), '/', ''), '-', '')
AS color_code_n,
brand_name,
category_id,
product_n,
color_code_n,
color_name,
QtyLocation3,
QtyLocation11
FROM och_products )
--the basic query, a bit more readable
SELECT
t1.*
FROM t1
left outer join t2
ON t2.brandid = t1.category_id
AND t2.categoryname = t1.brand_name
AND t2.productname = t1.product_n
AND t2.colorcode = t1.color_code_n
WHERE t2. brandid IS NULL
AND (t1.QtyLocation3 > 0
OR t1.QtyLocation11 > 0 )
ORDER BY
t1.brand_name, t1.product_n ASC

thanks, this worked it seems bit lengthy process then the other one

Paul White SSC Guru Points: 150467 More actions · Answer 5

biren (5/31/2010)
thanks, this worked it seems bit lengthy process then the other one

I think it's just better laid-out 🙂

My guess would be that Lowell presented it as clearly as possible to make it easy to work with.

It's a handy hint that one - nobody likes reading the sort of query in the first post on this thread 🙁

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi