Solved

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

Posted on 2011-02-13
7
205 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now