?
Solved

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

Posted on 2011-02-13
7
Medium Priority
?
211 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 2000 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

764 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