Solved

SQL query to return all records from one table when records in corresponding table do not match.

Posted on 2011-02-13
7
208 Views
Last Modified: 2012-05-11
Hi,

Using the sample data below, I want to get all records from IM_ITEM where

The RETAIL_FAMILY_COD  is the same and all corresponding records in IM_PRC.PRC_1 are not the same.  

So with the sample data given, the result set would be all records except these three, as they do all have the same PRC_1.

'1000001610', '1000001611', '1000001612'

DECLARE @IM_ITEM TABLE
    (ITEM_NO varchar(10) NOT NULL,
     DESCR varchar(50) NOT NULL,
     BRAND varchar(10) NOT NULL,
     RETAIL_FAMILY_CODE char(2) NOT NULL,
     ITEM_VEND_NO varchar(10) NOT NULL)


DECLARE @PO_VEND_ITEM TABLE
    (ITEM_NO varchar(10) NOT NULL,
     PRC_1 money NOT NULL,
     LOC_ID varchar(10) NOT NULL)


INSERT  @IM_ITEM
        (ITEM_NO, DESCR, BRAND, RETAIL_FAMILY_CODE, ITEM_VEND_NO)
VALUES  ('1000001608', 'SOUP A’, 'AMYS', 'F1', 'NATUBEST'),
        ('1000001609', 'SOUP B', 'AMYS', 'F1', 'MOUNPEOP'),
        ('1000001610', 'CHILI A', 'AMYS', 'F2', 'NATUBEST'),
        ('1000001611', 'CHILI B', 'AMYS', 'F2', 'NATUBEST'),
        ('1000001612', 'CHILI C', 'AMYS', 'F2', 'NATUBEST'),
        ('1000001613', 'ICE CREAM A', 'AMYS', 'F3', 'NATUBEST'),
        ('1000001614', 'ICE CREAM B', 'AMYS', 'F3', 'NATUBEST'),
        ('1000001615', 'ICE CREAM C', 'AMYS', 'F3', 'NATUBEST')


INSERT  @IM_PRC
        (ITEM_NO, PRC_1, LOC_ID)
VALUES  ('1000001608', ‘2.99’, '40'),
        ('1000001609', '2.59', '40'),
        ('1000001610', '8.99', '40'),
        ('1000001611', '8.99', '40'),
        ('1000001612', '8.99', '40'),
        ('1000001613', '10.99', '40'),
        ('1000001614', '10.89', '40'),
        ('1000001615', '10.99', '40')

Open in new window

0
Comment
Question by:AD1080
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34885092
can this one work for you?

SELECT * FROM @IM_ITEM
WHERE RETAIL_FAMILY_CODE IN 
(
	SELECT RETAIL_FAMILY_CODE
	FROM @IM_ITEM item
	INNER JOIN @IM_PRC prc ON item.ITEM_NO = PRC.ITEM_NO
	GROUP BY RETAIL_FAMILY_CODE
	HAVING NOT (COUNT(DISTINCT PRC_1) = 1 AND COUNT(*) > 1)
) 

Open in new window

0
 

Author Comment

by:AD1080
ID: 34885203
Hi JoeNuvo,

Sorry, no, this is returning every record on file.

0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 34885239
then, can you give more sample data + expected result?
since given example, come with imply explanation that
because all F2 have same 8.99 PRC, so it have to be exclude from result.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34885260
>>Sorry, no, this is returning every record on file. <<
Really?  Did you check it?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34885266
This is what I get using the solution provided.  It certainly does not look like "every record on file":
ITEM_NO	DESCR	BRAND	RETAIL_FAMILY_CODE	ITEM_VEND_NO
1000001608	SOUP A	AMYS	F1	NATUBEST
1000001609	SOUP B	AMYS	F1	MOUNPEOP
1000001613	ICE CREAM A	AMYS	F3	NATUBEST
1000001614	ICE CREAM B	AMYS	F3	NATUBEST
1000001615	ICE CREAM C	AMYS	F3	NATUBEST

Open in new window

0
 

Author Comment

by:AD1080
ID: 34885566
Sorry for the delayed reply.

I will look at this later tonight.  

0
 

Author Closing Comment

by:AD1080
ID: 34885876
Hi,

Sorry, I see that your solution works for my sample data.

There is an inconsistency with my actual data that is making it not work.  I will re post my question with revised sample data if I can't work it out.

thanks for your help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

707 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