October 27, 2016 at 3:47 am
Hi SQL Gurus,
I need help with an SQL query. I have a "Master list table (X and Y categories)". I have to compare the above list with two Sub list tables - "List X" and "List Y"
Table Master List
IDCatStatus
101XReady
102XReady
103YDispatched
104XDispatched
105YDispatched
106XReady
107XDispatched
108YReady
109XDispatched
110YDispatched
111XReady
112XDispatched
113XDispatched
2 Sub list tables as below
List X
ID
101
102
106
109
112
List Y
ID
105
110
I am trying to create a query which outputs:
Cat-X IDs which are not available in List-X
Cat-Y IDs which are not available in List-Y
Output
IDCatStatus
103YDispatched
104XDispatched
107XDispatched
108YReady
111XReady
113XDispatched
Thanks,
Ravi.
October 27, 2016 at 4:57 am
Welcome to SQL Server Central. When posting, it's always helpful to provide DDL's and INSERT statement for your tables and data, rather than a copy and paste. Have a look at the link to Jeff's post in my Signature, but you can see how I would have done it for your question below.
This should, however, get you what you're looking for:
CREATE TABLE #MasterList (ID INT,
Cat CHAR,
[Status] VARCHAR(15));
INSERT INTO #MasterList
VALUES (101, 'X', 'Ready'),
(102, 'X', 'Ready'),
(103, 'Y', 'Dispatched'),
(104, 'X', 'Dispatched'),
(105, 'Y', 'Dispatched'),
(106, 'X', 'Ready'),
(107, 'X', 'Dispatched'),
(108, 'Y', 'Ready'),
(109, 'X', 'Dispatched'),
(110, 'Y', 'Dispatched'),
(111, 'X', 'Ready'),
(112, 'X', 'Dispatched'),
(113, 'X', 'Dispatched');
CREATE TABLE #ListX (ID INT);
INSERT INTO #ListX
VALUES (101),
(102),
(106),
(109),
(112);
CREATE TABLE #ListY (ID INT);
INSERT INTO #ListY
VALUES (105),
(110);
SELECT ML.ID,
ML.Cat,
ML.[Status]
FROM #MasterList ML
LEFT JOIN #ListX X ON ML.ID = X.ID AND ML.Cat = 'X'
LEFT JOIN #ListY Y ON ML.ID = Y.ID AND ML.Cat = 'Y'
WHERE X.ID IS NULL
AND Y.ID IS NULL
ORDER BY ML.ID;
DROP TABLE #MasterList;
DROP TABLE #ListX;
DROP TABLE #ListY;
Edit: Icky alignment!!!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 27, 2016 at 8:47 am
Depending on what indexes you have in place, one of these version may perform better.
SELECT ml.ID, ml.Cat, ml.Status
FROM #MasterList ml
WHERE NOT EXISTS (
SELECT 1
FROM #ListX lx
WHERE ml.ID = lx.ID
AND ml.Cat = 'X'
UNION ALL
SELECT 1
FROM #ListY ly
WHERE ml.ID = ly.ID
AND ml.Cat = 'Y'
)
SELECT ml.ID, ml.Cat, ml.Status
FROM #MasterList ml
LEFT JOIN (
SELECT lx.ID, 'X' AS Cat
FROM #ListX lx
UNION ALL
SELECT ly.ID, 'Y' AS Cat
FROM #ListY ly
) l
ON ml.ID = l.ID
AND ml.Cat = l.Cat
WHERE l.ID IS NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 28, 2016 at 8:20 pm
I need help with an SQL query. I have a "Master list table (X and Y categories)". I have to compare the above list with two Sub list tables - "List X" and "List Y"
Did you read the forum posting rules? Where is the DDL? What are the keys? What you did post has a lot of fundamental design problems. There is no such thing as a generic "_id", generic "_cat", or a generic "_status" in RDBMS. These are what the ISO 11179 standards call an attribute property; it has to be attached to a an attribute, just as an adjective must modify a noun.
Next the term "Master" is not used in RDBMS. That term was used for tape files and some of the early network databases. It still lives on and poorly designed SQL databases, however. Let us try to fix what you have got.
CREATE TABLE Shipments
(shipment_id CHAR(3) NOT NULL PRIMARY KEY, -- wild guess!
shipment_category CHAR(1) NOT NULL
CHECK (shipment_category IN ('X', 'Y')),
shipment_status CHAR(10) NOT NULL
CHECK(shipment_status IN ('Ready', 'Dispatched'))
);
Table Master List
IDCatStatus
101XReady
102XReady
103YDispatched
104XDispatched
105YDispatched
106XReady
107XDispatched
108YReady
109XDispatched
110YDispatched
111XReady
112XDispatched
113XDispatched
Why did you physically materialized to redundant tables? The goal of all databases (not just RDBMS) is to reduce redundancy, not increase it. What you have would better be done with views; in SQL views are just as much a table as a materialize base table. I think your mindset is still back in the days of decks of punch cards and Magnetic tape files.
CREATE VIEW X_Shipments
AS
SELECT shipment_id, shipment_status
FROM Shipments
WHERE shipment_category = 'X';
CREATE VIEW Y_Shipments
AS
SELECT shipment_id, shipment_status
FROM Shipments
WHERE shipment_category = 'Y';
Did you notice how the views are always correct? There is no need do any updates or inserts on them yourself. It looks like what you have done is come up with an improper design. If these little decks of punch cards that you have created off to the side have meaning, then that should be encoded in the category. I am going to make a guess, since I have no DDL, that what you really wanted was more like this.
CREATE TABLE Shipments
(shipment_id CHAR(3) NOT NULL PRIMARY KEY, -- wild guess!
shipment_category CHAR(2) NOT NULL
CHECK (shipment_category IN ('X1', 'Y1', 'X2', 'Y2')),
shipment_status CHAR(10) NOT NULL
CHECK(shipment_status IN ('Ready', 'Dispatched'))
);
INSERT INTO Shipments
VALUES
('101', 'X1', 'Ready'),
('102', 'X1', 'Ready'),
('103', 'Y2', 'Dispatched'),
('104', 'X2', 'Dispatched'),
('105', 'Y1', 'Dispatchd'),
('106', 'X1', 'Ready'),
('107', 'X2', 'Dispatched'),
('108', 'Y2', 'Ready'),
('109', 'X1', 'Dispatched'),
('110', 'Y1', 'Dispatched'),
('111', 'X2', 'Ready'),
('112', 'X1', 'Dispatched'),
('113', 'X3', 'Dispatched');
What you did is called attribute splitting. It means your design took something that should have been in one column and spread it out over two columns or multiple tables. Would you put the shoe width in one place and the shoe length in another? Of course not! This is one attribute, and it would appear and a column named "shoe_size" in a properly designed schema.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply