July 15, 2011 at 3:08 am
Hello,
I'm a pharmacist and an SQL newbie so take it easy on me. Here's a general outline of the problem:
col1 col2 col3 col4 etc...
1 x1
1 f2
2 f1
3 x3
3 x4
3 f2
There are other columns too, but I only need to compare 2 out of all of them. Both col1 & col2 have many potential choices (eg many data, not just 2-3 options). But the result I'm looking for is:
if col2 has data "f%" but no "x%" for the exact same data for col1, then I need to know all col1 data that fits. So, the result of the query should be "2, f" from above since the col1 row3 does have an f% in col2, but no x% also that pertains to the same data in col1 (eg the #2).
I may have sounded confusing. I've never built a table so I don't know how to give you a start. I've tried using a subquery of the same table on itself, but get too many results b/c of other columns and non-similar data. I can't join them w/o redundancy. I wish it was as simple as 'select col1 where col2 contains f% but <> x%' or something, but it is much more complex to me and I cannot wrap my head around it. I don't know how to "compare" one column to another, and haven't had much luck reading other posts and info online.
Please help if this makes sense, thanks for your time.
July 15, 2011 at 3:15 am
Are you looking for something like this?
declare @tbl table
(col1 int, col2 char(2))
insert into @tbl
SELECT 1,'x1' UNION ALL
SELECT 1 ,'f2' UNION ALL
SELECT 2 ,'f1' UNION ALL
SELECT 3 ,'x3' UNION ALL
SELECT 3 ,'x4' UNION ALL
SELECT 3 ,'f2'
SELECT * FROM @tbl t1
WHERE EXISTS (SELECT 1 FROM @tbl t2 WHERE t2.col1=t1.col1 AND t2.col2 LIKE 'f%')
AND NOT EXISTS (SELECT 1 FROM @tbl t2 WHERE t2.col1=t1.col1 AND t2.col2 LIKE 'x%')
July 15, 2011 at 3:44 am
You da man. That worked really great except I didn't explain enough of what I needed. Here's what I have:
select a.DrugID,a.InventoryID from DPhaDrugInventories a
where exists (select * from DPhaDrugInventories b where a.DrugID = b.DrugID and b.InventoryID like 'FLR%')
and not exists (select * from DPhaDrugInventories b where a.DrugID = b.DrugID and b.InventoryID like 'PX%')
order by DrugID
And that gets me all the DrugID's where they are in flr% but not px% from this table. But that appears to be too selective. I did not give enough info, but my data set may contain:
col1 col2
1 f1
1 f2
1 p2
1 f3
1 p3
So, the code above will not give "1,f1" as an answer, b/c of course col1 data "1" is in a "p%" in other rows. Since, in my table, the DrugID = ~9000 options and the InventoryID =~80 options, I don't think it's possible to specify each example. But a comparison using 'like' might not work I guess either eh? Since the col2 "f's & p's" do not match I just can't figure out how to get the info I need. Is there any way I can give you more info or help out? Thanks...
July 15, 2011 at 9:29 am
Please stay consistent with your explanation.
In your original example you used 'f%' but not 'x%'. In your latest reply you mentioned 'flr%' but not 'px%' presenting sample code that doesn't inlcude either one and immediately jump to not give "1,f1" as an answer, b/c of course col1 data "1" is in a "p%" in other rows.
It's impossible to guess what you're looking for.
If you find a business logic that can be described as "must contain 'x%' but not 'y%'" then you can use the sample I posted. If not, you need to describe the requirement more precisely.
July 16, 2011 at 12:50 am
My bad. You are correct in that my description is lackluster. The exact definition of what I need hasn't come to my attention yet, which is why I'm having a hard time with the query. How can you get the results you want if you can't define exactly what you need? I'm not that good (obviously) at thinking like SQL.
Here's some real data from the table:
DrugID InventoryID
MAGN2IVPBFLR-4N
MAGN2IVPBFLR-5N
MAGN2IVPBFLR-6N
MAGN2IVPBFLR-7N
MAGN2IVPBFLR-8N
MAGN2IVPBPX-4N
MAGN2IVPBPX-4W
MAGN2IVPBPX-6E
MAGN2IVPBPX-7E
MAGN2IVPBPX-7N
MAGN2IVPBPX-8N
There are many DrugID's. Notice in the info above, the DrugID is present in "FLR-5N" but not in a "PX" relating to "5" anything, which is what concerns me. That's the discrepancy I'm trying to locate. I can see clearly now the initial way you helped me was not even close to the exactness needed for the query to be helpful, due to my poor understanding of the exactness needed. But the problem remains: I cannot find an exact description of what I need to be useful. There are too many variables where selecting one will limit another. Perhaps this problem cannot be solved by a simple query?
Perhaps I will have to make it fit a much larger written out query, like:
"select drugid where locationid = FLR-5N and locationid doesn't contain "PX-5E, PX-5N, PX-5W (which are the only PX- locations that concern me relating to only FLR-5N)"", and then repeat this type of code for all the "FLR" locations that concern me? That would be a lot of redundant-type code, but perhaps being that specific is the only way to get what I want. I was hoping it would be more simple and less code-lengthy.
Would uploading an .xls file of the table help elucidate the issue any? At any rate, I do appreciate your help, and apologize for not being as helpful or understanding of the issues as you would like...
July 16, 2011 at 2:48 am
The most important thing is to find the pattern.
It certainly would help to get a longer list of values where the rows to find are already identified including a short description why those need to be returned.
If you do so, make sure to obfuscate the values, but to keep the search pattern. (easier said than done, I know...)
July 17, 2011 at 10:18 pm
OK, I'm getting closer, but still need help. Here's what I have:
select DrugID,InventoryID
from DPhaDrugInventories a
where exists (select distinct b.DrugID from DPhaDrugInventories b where a.DrugID = b.DrugID
and b.InventoryID = 'FLR-4N')
and not exists (select distinct b.DrugID from DPhaDrugInventories b where a.DrugID = b.DrugID
and b.InventoryID like 'PX-4%')
--OR exists (select distinct b.InventoryID from DPhaDrugInventories b where a.DrugID = b.DrugID
--and b.InventoryID = 'FLR-5N')
--and not exists (select * from DPhaDrugInventories b where a.DrugID = b.DrugID
--and b.InventoryID like 'PX-5%')
--and InventoryID in ('FLR-4N','FLR-5N','FLR-6N','FLR-7N','FLR-8N')
order by DrugID
And forget the text-out portion for now, it gets me something this:
CEFT1PIGFLR-4N
CEFT1PIGFLR-5N
CEFT1PIGFLR-6N
CEFT1PIGFLR-7N
CEFT1PIGFLR-8N
CEFT1PIGPX-6E
CEFT1PIGPX-8N
CEFT1PIGPX-CCU
CEFT1PIGPX-CSOR
CEFT1PIGPX-EDSOU
CEFT1PIGPX-ER
So the query is getting what I want, but too much. I'm looking for only the 1 line in the data above that includes "FLR-4N". I only want to see "CEFT1PIG" once, not all the other rows that don't apply (to me). I understand why I'm getting all the other data, because that same CEFT1PIG does exist in all those places, but I only want the one row where "b.InventoryID = 'FLR-4N'", not all the others.
Now if I start my initial select statement with "select distinct DrugID" ONLY, and not both "DrugID, InventoryID", then I get exactly the list of DrugID's I need. But then, of course, I don't get the "InventoryID" column that relates with it, which I also need for informational purposes. And as you can see, I plan to have to multiple "OR exists" statements afterwards to get all the info I desire, so I cannot assume all the "InventoryID's" are the same.
How can I select only the 1 row that applies in the example above, and have it work with the multiple "texted out" statements I will add accordingly? The solution must be simple but again it escapes me. Thanks for your time.
July 18, 2011 at 10:44 am
something like this?
select DrugID,InventoryID
from DPhaDrugInventories a
join DPhaDrugInventories b on a.DrugID = b.DrugID
where
(
b.InventoryID = 'FLR-4N'
or b.InventoryID like 'PX-4%'
or b.InventoryID = 'FLR-5N'
or b.InventoryID like 'PX-5%'
)
and InventoryID in ('FLR-4N','FLR-5N','FLR-6N','FLR-7N','FLR-8N')
order by DrugID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2011 at 10:47 am
Actually reading further i think this might be closer...
select DrugID,InventoryID
from DPhaDrugInventories a
join DPhaDrugInventories b on a.DrugID = b.DrugID
where b.InventoryID like 'PX-4%'
or b.InventoryID like 'PX-5%'
or InventoryID in ('FLR-4N','FLR-5N','FLR-6N','FLR-7N','FLR-8N')
order by DrugID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2011 at 4:22 pm
I'm thinking we're not actually looking for a hard coded answer here, folks. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2011 at 4:36 pm
Is this what you are looking for?
You may need to tweak the EXISTS statement to handle InventoryIDs that have more than 1 digit...
;with data(DrugID, InventoryID)
as
(
select 'MAGN2IVPB','FLR-4N' union all
select 'MAGN2IVPB','FLR-5N' union all
select 'MAGN2IVPB','FLR-6N' union all
select 'MAGN2IVPB','FLR-7N' union all
select 'MAGN2IVPB','FLR-8N' union all
select 'MAGN2IVPB','PX-4N' union all
select 'MAGN2IVPB','PX-4W' union all
select 'MAGN2IVPB','PX-6E' union all
select 'MAGN2IVPB','PX-7E' union all
select 'MAGN2IVPB','PX-7N' union all
select 'MAGN2IVPB','PX-8N'
)
select d1.DrugID, d1.InventoryID
from data as d1
where d1.InventoryID like 'FLR-%'
and not exists(select 1 from data as d2 where d2.DrugID = d1.DrugID and d2.InventoryID like 'PX-'+SUBSTRING(d1.InventoryID,5,1)+'%')
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 18, 2011 at 5:34 pm
My solution is a whole lot longer but, I believe, it may be easier for a newbie to understand and, possibly, maintain. It also gives a full accounting if either a FLR or PX is missing and it accounts for multiple digits in either.
First... take a look at the article at the first link in my signature line at the bottom of this post. It'll show you how to get answers much quicker and without having to go back and forth, post after post, trying to figure out what you're doing. It'll also entice people to work on your problems more quickly because they won't have to spend any time setting up a test for their code.
So... we need "readily consumable" test data. You are kind of new, so I did it for you... this time. 😉
--===== Create a test table. This is NOT a part of the solution.
-- It SHOULD have been a part of the problem description
CREATE TABLE #DrugInventory
(
DrugID VARCHAR(10),
InventoryID VARCHAR(10)
)
;
--===== Populate the test table. This is NOT a part of the solution.
-- It SHOULD have been a part of the problem description
INSERT INTO #DrugInventory
(DrugID, InventoryID)
SELECT 'MAGN2IVPB','FLR-4N' UNION ALL
SELECT 'MAGN2IVPB','FLR-5N' UNION ALL
SELECT 'MAGN2IVPB','FLR-6N' UNION ALL
SELECT 'MAGN2IVPB','FLR-7N' UNION ALL
SELECT 'MAGN2IVPB','FLR-8N' UNION ALL
SELECT 'MAGN2IVPB','PX-4N' UNION ALL
SELECT 'MAGN2IVPB','PX-4W' UNION ALL
SELECT 'MAGN2IVPB','PX-6E' UNION ALL
SELECT 'MAGN2IVPB','PX-7E' UNION ALL
SELECT 'MAGN2IVPB','PX-7N' UNION ALL
SELECT 'MAGN2IVPB','PX-8N' UNION ALL
SELECT 'CEFT1PIG','FLR-4N' UNION ALL
SELECT 'CEFT1PIG','FLR-5N' UNION ALL
SELECT 'CEFT1PIG','FLR-6N' UNION ALL
SELECT 'CEFT1PIG','FLR-7N' UNION ALL
SELECT 'CEFT1PIG','FLR-8N' UNION ALL
SELECT 'CEFT1PIG','PX-6E' UNION ALL
SELECT 'CEFT1PIG','PX-8N' UNION ALL
SELECT 'CEFT1PIG','PX-CCU' UNION ALL
SELECT 'CEFT1PIG','PX-CSOR' UNION ALL
SELECT 'CEFT1PIG','PX-EDSOU' UNION ALL
SELECT 'CEFT1PIG','PX-ER'
;
The following code is in 5 easy to understand sections. The explanation for each is in the code below in the form of comments. With the final WHERE clause commented out, you get the full report of all items and whether or not each FLR instance as some form of PX match or not. Un-commenting the final WHERE clause will give a report of only discrepancies.
WITH
cteFlrFirstPass AS
( --=== This returns DrugID, InventoryID, and finds the first significant digit
-- and everything after that on the InventoryID for FLR items.
SELECT DrugID,
InventoryID,
InventoryNumber = SUBSTRING(InventoryID,5,10)
FROM #DrugInventory di
WHERE InventoryID LIKE 'FLR-[0-9]%'
)
,
cteFLR AS
( --=== The again returns DrugID and InvetoryID but also finally
-- isolates an unknown number of digits after the dash from the
-- date in the CTE above (cteFlrFirstPass).
SELECT DrugID,
InventoryID,
InventoryNumber = SUBSTRING(InventoryNumber,1,PATINDEX('%[0-9][^0-9]%',InventoryNumber))
FROM cteFlrFirstPass
)
,
ctePxFirstPass AS
( --=== This returns DrugID, InventoryID, and finds the first significant digit
-- and everything after that on the InventoryID for PX items.
SELECT DrugID,
InventoryID,
InventoryNumber = SUBSTRING(InventoryID,4,10)
FROM #DrugInventory di
WHERE InventoryID LIKE 'PX-[0-9]%'
)
,
ctePX AS
( --=== The again returns DrugID and InvetoryID but also finally
-- isolates an unknown number of digits after the dash from the
-- date in the CTE above (cteFlrFirstPass).
SELECT DrugID,
InventoryID,
InventoryNumber = SUBSTRING(InventoryNumber,1,PATINDEX('%[0-9][^0-9]%',InventoryNumber))
FROM ctePxFirstPass
) --=== This build a "full" report from the cteFLR and ctePX CTE's above.
-- Since it uses a FULL OUTER JOIN, it'll find discrepancies on
-- "both sides of the fence".
-- Uncomment the WHERE clause to get just a list of discrepancies.
SELECT flrDrugID = flr.DrugID, flrInventoryID = flr.InventoryID,
pxDrugID = px.DrugID , pxInventoryID = px.InventoryID
FROM cteFlr flr
FULL OUTER JOIN ctePx px
ON flr.DrugID = px.DrugID
AND flr.InventoryNumber = px.InventoryNumber
-- WHERE flr.DrugID IS NULL
-- OR px.DrugID IS NULL
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2011 at 6:27 am
Hello,
as I understand, in your drug inventory you put drugs and locations; each drug in a location 'FLR-%' must be present in another location 'PX-%' and both locations must be linked in some way.
So I think you need a relation table, linking each FLR location with a collection of related PX locations; then you can access your data searching for drugs with a missing related location.
Try this code:
WITH DrugInventory (DrugID, InventoryID) AS (
SELECT 'MAGN2IVPB','FLR-4N' UNION ALL
SELECT 'MAGN2IVPB','FLR-5N' UNION ALL
SELECT 'MAGN2IVPB','FLR-6N' UNION ALL
SELECT 'MAGN2IVPB','FLR-7N' UNION ALL
SELECT 'MAGN2IVPB','FLR-8N' UNION ALL
SELECT 'MAGN2IVPB','PX-4N' UNION ALL
SELECT 'MAGN2IVPB','PX-4W' UNION ALL
SELECT 'MAGN2IVPB','PX-6E' UNION ALL
SELECT 'MAGN2IVPB','PX-7E' UNION ALL
SELECT 'MAGN2IVPB','PX-7N' UNION ALL
SELECT 'MAGN2IVPB','PX-8N' UNION ALL
SELECT 'CEFT1PIG','FLR-4N' UNION ALL
SELECT 'CEFT1PIG','FLR-5N' UNION ALL
SELECT 'CEFT1PIG','FLR-6N' UNION ALL
SELECT 'CEFT1PIG','FLR-7N' UNION ALL
SELECT 'CEFT1PIG','FLR-8N' UNION ALL
SELECT 'CEFT1PIG','PX-6E' UNION ALL
SELECT 'CEFT1PIG','PX-8N' UNION ALL
SELECT 'CEFT1PIG','PX-CCU' UNION ALL
SELECT 'CEFT1PIG','PX-CSOR' UNION ALL
SELECT 'CEFT1PIG','PX-EDSOU' UNION ALL
SELECT 'CEFT1PIG','PX-ER'
)
,
InventoryRelations (InventoryId, RelInventoryId) AS (
SELECT 'FLR-4N', 'PX-4N' UNION ALL
SELECT 'FLR-4N', 'PX-4W' UNION ALL
SELECT 'FLR-5N', 'PX-5W' UNION ALL
SELECT 'FLR-6N', 'PX-6E' UNION ALL
SELECT 'FLR-7N', 'PX-7E' UNION ALL
SELECT 'FLR-7N', 'PX-7N' UNION ALL
SELECT 'FLR-8N', 'PX-8N'
)
SELECT Drug1.DrugId, Drug1.InventoryId, REL.RelInventoryId, Drug2.DrugId
FROM DrugInventory Drug1
LEFT JOIN InventoryRelations REL
ON Drug1.InventoryId = REL.InventoryId
LEFT JOIN DrugInventory Drug2
ON Drug1.DrugId = Drug2.DrugId AND Drug2.InventoryId = REL.RelInventoryId
NULL values in [RelInventoryId] points a missed relation; NULL values in the second [DrugId] points a drug that has a first location but not the correspondent related location, I think that this is what you are searching for.
Regards,
Francesc
July 19, 2011 at 10:35 pm
@mister.magoo- TY sir, this style query worked well for my current needs and is simple enough for me to wrap my head around after thinking about what it is saying for a bit.
@jeff Moden and frfernan- TY too. You guys also gave me good examples of different techniques. Unfortunately I don't know what you're talking about, ha! I've been studying the examples and reading up on things I've never seen before, eg- CTE's. Boy those seem helpful, but before Jeff's suggestion I hadn't ever used one or understood them. Apparently I cannot explain the depth of my noobishness, but I love trying. I will keep learning from your examples.
I really appreciate everyone's help, this is such a great site. The few questions I've ever asked are always easily completed by the guru's that roam around. What a great resource, and I AM learning a lot from these examples. I wish I could find someone in the Springfield IL area to teach me 1on1 sometime, that is the best way to learn.
I try to read the built-in sql help and it is like greek to me, w/ poor examples. I scour online content which has helped a lot, but it is still insufficient. Examples like the above from people who know what they're doing are the best way for me to parse sql processes and ascertain good info. I sure appreciate it guys, thanks you,
Josh
July 20, 2011 at 5:03 am
pharmboy4u (7/19/2011)
@mister.magoo- TY sir, this style query worked well for my current needs and is simple enough for me to wrap my head around after thinking about what it is saying for a bit.
Glad to help 😀 - if you need any help understanding how or why anything is working, just ask!
I really appreciate everyone's help, this is such a great site.
I agree - I have come on leaps and bounds this last year thanks to SSC and the great people here - long may it last!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply