April 10, 2017 at 10:44 pm
Kind of a weird little query I need to devise where as an order from my orders table needs to meet a few criteria in its own table while it has to check another table to meet a condition that doesnt have a certain value. I am wanting a list that contains unique customerids and sourceids that do have suspended or tagged in their status columns , do not have an empty or null in their ordertype, but in the corresponding sourceid in the Codes table , they cannot have a 100 code :
.:
TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
sourceid (varchar(12) NOT NULL),
Status varchar(50) NULL,
ordertype varchar(20) NULL,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
TABLE dbo.codes --cod
(
codeID (pk uniqueidentifier NOT NULL),
sourceid (varchar(12) NOT NULL),
confirmed datetime NULL,
code (varchar(5) NULL),
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC)
);
....as far as meeting all these conditions, i have become stumped on how to include these sourceids with their customerids that are in the same row that do not have a 100 code
select o.customerid,o.sourceid from orders as o where status in ('suspended','tagged') and o.ordertype <> '' and o.customerid in (select cod.orderid from codes as cod where code <> 100)
?
Zo
April 11, 2017 at 12:13 am
use a correlated subquery and [NOT] EXISTS to find parent records with/without child records. Just too lazy/tired to sort out the mess without sample data.
April 11, 2017 at 1:40 pm
How about something like this:CREATE TABLE dbo.orders (
OrderID int NOT NULL,
CustomerID varchar(5) NOT NULL,
sourceid varchar(12) NOT NULL,
[Status] varchar(50) NULL,
ordertype varchar(20) NULL,
ordershipped datetime NULL,
orderarrived datetime NULL,
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
CREATE TABLE dbo.codes (
codeID uniqueidentifier NOT NULL,
sourceid varchar(12) NOT NULL,
confirmed datetime NULL,
code varchar(5) NULL,
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC)
);
SELECT DISTINCT o.customerid, o.sourceid
FROM orders AS o
LEFT OUTER JOIN dbo.codes AS cod
ON o.sourceid = cod.sourceid
AND cod.code <> '100'
WHERE o.[Status] IN ('suspended', 'tagged')
AND ISNULL(o.ordertype, '') <> ''
DROP TABLE dbo.codes;
DROP TABLE dbo.orders;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply