Solved

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

Posted on 2011-02-13
7
207 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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