Solved

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

Posted on 2011-02-13
7
201 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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