October 25, 2016 at 7:34 am
Hi,
I am having issues with the following script. I have 3 tables that link together.
What I need to do is find owners that have items with statuses as 7 and (1-15).
SELECT O.OW_ID
, O.OW_TITLE
, O.OW_FORENAMES
, O.OW_SURNAME
, COUNT(*)
FROM PROPS.DP_OWNER O
JOIN PROPS.DP_ENTRY E
ON O.OW_ID = E.DP_PERSON
JOIN PROPS.DP_ITEM I
ON E.DP_ID = I.DPI_DP_ID
WHERE I.DPI_STATUS IN ('7') --DISPOSED
-- I was thinking something might go here to say and?
GROUP BY O.OW_ID
, O.OW_TITLE
, O.OW_FORENAMES
, O.OW_SURNAME
Thanks in advance for any help!!
Paul
October 25, 2016 at 7:47 am
dramaqueen (10/25/2016)
Hi,I am having issues with the following script. I have 3 tables that link together.
What I need to do is find owners that have items with statuses as 7 and (1-15).
SELECT O.OW_ID, O.OW_TITLE
, O.OW_FORENAMES
, O.OW_SURNAME
, COUNT(*)
FROM PROPS.DP_OWNER O
JOIN PROPS.DP_ENTRY E
ON O.OW_ID = E.DP_PERSON
JOIN PROPS.DP_ITEM I
ON E.DP_ID = I.DPI_DP_ID
WHERE I.DPI_STATUS IN ('7') --DISPOSED
-- I was thinking something might go here to say and?
GROUP BY O.OW_ID
, O.OW_TITLE
, O.OW_FORENAMES
, O.OW_SURNAME
Thanks in advance for any help!!
Paul
Change this line
WHERE I.DPI_STATUS IN ('7') --DISPOSED
to this?
WHERE I.DPI_STATUS IN ('7', '(1-15)')
If that's not what you meant, please try to make your requirement clearer, so that someone who is not familiar with your data can understand.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 25, 2016 at 8:02 am
Hi,
Sorry in more detailed terms;
There is a table with items, these are linked with owners through the entry table.
One owner can have multiple items and each of the items have a status code. 7 means disposed.
I need a query that has the ability to identify owners that have items with both at least 1 item with status code 7 and at least one other item with status code that is between 1 - 15 excluding the 7.
I hope this makes more sense.
Thanks,
Paul
October 25, 2016 at 8:02 am
it's always best to give some more data that your query, it's very hard to do anything without data (see the link in my signature for how to post).
I've gone for a somewhat different approach, where grouping isn't required. not sure which is more effecient, as i haven't tested.
CREATE TABLE #Owner (OwnerID INT IDENTITY(1,1),
Title VARCHAR(5) NOT NULL,
Forenames VARCHAR(100) NOT NULL,
Surname VARCHAR(50) NOT NULL);
INSERT INTO #Owner (Title, Forenames, Surname)
VALUES ('Mr','John','Smith'),
('Mr','Dave','Bloggs'),
('Ms','Clare','Wallis'),
('Dr','Angela','Green');
--I have no idea what an Item is, so I'm making something up completely
CREATE TABLE #Entry (EntryID INT IDENTITY(1,1),
PersonID INT NOT NULL,
EntryDate DATETIME2(0) NOT NULL);
INSERT INTO #Entry (PersonID, EntryDate)
VALUES (1, '2016-10-20 12:13:24'),
(1, '2016-10-21 19:22:17'),
(2, '2016-10-21 14:25:59'),
(3, '2016-10-23 09:01:16'),
(3, '2016-10-24 12:07:46'),
(3, '2016-10-24 15:52:01');
--Again, I have no idea what an Item is, so this is pure guesswork.
CREATE TABLE #Item (ItemID INT IDENTITY(1,1),
ItemEntryID INT NOT NULL,
ItemName VARCHAR(50) NOT NULL,
ItemStatus INT NOT NULL);
INSERT INTO #Item (ItemEntryID, ItemName, ItemStatus)
VALUES (1, 'Cheese', 2),
(1, 'Bacon', 2),
(1, 'Lettuce', 4),
(2, 'Sausages', 7),
(2, 'lager', 6),
(3, 'Tomatoes', 15),
(4, 'Light Bulb', 12),
(4, 'Sausages', 12),
(5, 'Hammock', 1),
(5, 'Aubergine', 9),
(6, 'Tissue', 7);
SELECT *
FROM #Owner O
JOIN #Entry E ON O.OwnerID = E.PersonID
JOIN #Item I ON E.EntryID = I.ItemEntryID;
SELECT O.OwnerID,
O.Title,
O.Forenames,
O.Surname
FROM #Owner O
WHERE EXISTS (SELECT sqi.ItemStatus
FROM #Entry sqe
JOIN #Item sqi ON sqe.EntryID = sqi.ItemEntryID
WHERE sqe.PersonID = O.OwnerID
AND sqi.ItemStatus = 7);
DROP TABLE #Owner;
DROP TABLE #Entry;
DROP TABLE #Item;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2016 at 8:20 am
I managed to solve it.
SELECT O.ow_id
FROM props.dp_owner O
JOIN props.dp_entry E
ON O.ow_id = E.dp_person
JOIN props.dp_item I
ON E.dp_id = I.dpi_dp_id
WHERE I.dpi_status NOT IN ( '7' ) --DISPOSED
AND O.ow_id IN (SELECT O.ow_id
FROM props.dp_owner O
JOIN props.dp_entry E
ON O.ow_id = E.dp_person
JOIN props.dp_item I
ON E.dp_id = I.dpi_dp_id
WHERE I.dpi_status IN ( '7' ) --DISPOSED
GROUP BY O.ow_id)
GROUP BY O.ow_id
October 25, 2016 at 8:41 am
dramaqueen (10/25/2016)
I managed to solve it.
SELECT O.ow_idFROM props.dp_owner O
JOIN props.dp_entry E
ON O.ow_id = E.dp_person
JOIN props.dp_item I
ON E.dp_id = I.dpi_dp_id
WHERE I.dpi_status NOT IN ( '7' ) --DISPOSED
AND O.ow_id IN (SELECT O.ow_id
FROM props.dp_owner O
JOIN props.dp_entry E
ON O.ow_id = E.dp_person
JOIN props.dp_item I
ON E.dp_id = I.dpi_dp_id
WHERE I.dpi_status IN ( '7' ) --DISPOSED
GROUP BY O.ow_id)
GROUP BY O.ow_id
That, will definitely be slow with a larger dataset.
EDIT: Amended using your naming conventions above:
SELECT O.ow_id
FROM props.dp_owner O
WHERE EXISTS (SELECT sqi.dpi_status
FROM props.dp_entry sqe
JOIN props.dp_item sqi ON sqe.dp_id = sqi.dpi_dp_id
WHERE sqe.dp_persoD = O.ow_id
AND sqi.dpi_status = 7);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2016 at 11:58 am
For best performance, avoid the separate EXISTS lookup as well:
SELECT O.ow_id
FROM props.dp_owner O
INNER JOIN props.dp_entry E
ON O.ow_id = E.dp_person
INNER JOIN props.dp_item I
ON E.dp_id = I.dpi_dp_id
GROUP BY O.ow_id
HAVING
/* at least 1 status '7' ... */
MAX(CASE WHEN I.dpi_status = '7' THEN 1 ELSE 0 END) = 1 AND
/* ... and at least one status '1' thru '15' not '7' */
MAX(CASE WHEN I.dpi_status IN ('1', '2', '3', '4', '5',
'6', '8', '9', '10', '11',
'12', '13', '14', '15') THEN 1 ELSE 0 END) = 1
Edit: Corrected typo in comments.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2016 at 12:08 pm
Hey,
Thanks for the replies. I will let you know when I am back on the server tomorrow.
Paul
October 26, 2016 at 12:26 pm
>> I am having issues with the following script. I have 3 tables that link together. <<
We do not use "link" in RDBMS; that is assembly language. We have references and we do joins, which are totally different concepts. If you would read the posting rules for the forum, you would have seen the DDL requirement. Now we have to guess at things. The first thing we notice is that your tables have only one row in them! Tables model sets; sets have a collective or plural name by their nature.
| Since the row in your result set contains only columns and aggregates from the owners table, why are you doing a join to these other tables? We have no idea what the keys are for any of the tables or anything else about them. I am bothered by things like vague "_id" column names. Who references who? How does someone perform the role of "_person" in the data model? In a valid data model things have precise names. And of also, data elements do not change names from table to table! SQL is based on logic and not violates the most fundamental law of logic; the Law of Identity.
Non-SQL programmers who think that links exist in RDBMS will fake pointer chains with GUID, IDENTITY or other non-relational devices. This is one of many reasons we need DDL. When I see things like "E.dp_id = I.dpi_dp_id" and "O.ow_id = E.dp_person", I get scared :w00t:
>> What I need to do is find owners that have items with statuses as 7 and (1-15). <<
Did you notice that these two conditions overlap? Perhaps you can do it with something like this (after you correct the DDL and get proper keys):
SELECT DISTINCT O.dp_id, O.ow_title, O.ow_fore_name, O.ow_sur_name
FROM Dp_Owners AS O
WHERE EXISTS(SELECT *
FROM Dp_Entries AS E,
Dp_Items AS I
WHERE O.dp_id = E.dp_id
AND E.dp_id = I.dp_id
AND I.dpi_status BETWEEN '01' AND '15');
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply