AD1080
asked on
SQL query to return all records from one table when records in corresponding table do not match.
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'
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')
ASKER
Hi JoeNuvo,
Sorry, no, this is returning every record on file.
Sorry, no, this is returning every record on file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Sorry, no, this is returning every record on file. <<
Really? Did you check it?
Really? Did you check it?
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
ASKER
Sorry for the delayed reply.
I will look at this later tonight.
I will look at this later tonight.
ASKER
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.
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.
Open in new window