August 7, 2010 at 6:56 am
Hi,
I have this problem: I need to get a group of records where a StatusID is included on a field from another table. Let' say: StatusID='ASC' from StatusTable table and I have another table StatusFilterTable with Filter field which have this value: "MNR,AQC,TVR,ASC,MVR"
I tried with this SP:
@status varchar(50)
AS
SELECT A.StatusID + ' - ' + A.StatusDescripcion AS StatusDescription
FROM StatusTable A
WHERE A.Activo=1 AND A.StatusID IN (SELECT B.Filter FROM StatusFilter B WHERE B.StatusID=@Status)
ORDER BY A.StatusID
RETURN
But it returns zero records. Any help will be really appreciated.
My best regards,
:unsure:
August 7, 2010 at 7:33 am
sergio.huertac
You are more likely to receive a tested answer if you include in your posting the Table definition(s), sample data and required results as described in the article whose link is in the first line of my signature block.
August 7, 2010 at 8:17 am
Ron:
These are the data which I want to receive for the sample previously sent :
Status
---------------------------------------------------------
MNR - Confirmation to Send
AQC - Partener Reason
TVR - Shipped Already
ASC - Pending to Send
MVR - On Hold
No rows affected.
(5 row(s) returned)
@RETURN_VALUE = 0
StatusTable Structure:
StatusID varchar(3)
StatusDescription varchar(50)
Data of StatusTable:
StatusID StatusDescription
MNR Confirmation to Send
AQC Partener Reason
TVR Shipped Already
ASC Pending to Send
MVR On Hold
StatusFilter Structure:
StatusID varchar(3)
Filter varchar(100)
Data of StatusFilter:
StatusID Filter
ASC 'ASC','DEL','PE','PNST'
CAD 'CAD','CNA','ED','EXT','IES'
Thank you for your prompt answer.:-)
August 7, 2010 at 7:08 pm
Ron has already asked you this...
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
If you're not going to post some "CREATE TABLE" and "INSERT INTO " statements, don't bother responding.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 8, 2010 at 7:51 am
sergio.huertac
To repeat what I and WayneS requested. This is an example of the Create Table and sample data as it should be posted to a forum.
CREATE TABLE #StatusFilter(
StatusID varchar(3),
Filter varchar(100))
INSERT INTO #StatusFilter
SELECT 'ASC', 'ASC,DEL,PE,PNST' UNION ALL
SELECT 'CAD', 'CAD,CNA,ED,EXT,IES'
Also note that you posted an INCOMPLETE structure for the Status table
Data of StatusTable:
StatusID StatusDescription
MNR Confirmation to Send
AQC Partener Reason
TVR Shipped Already
ASC Pending to Send
MVR On Hold
A specific example of an incomplete table definition
a. Is the MNR column CHAR, VARCHAR, INT, Bit ?
b. This same question can be asked for the additional columns.
And of course there was no sample data for the status table.
Let me repeat a portion of WayneS' comment:
please HELP US HELP YOU.
August 8, 2010 at 8:27 am
sergio.huertac (8/7/2010)
I have this problem: I need to get a group of records where a StatusID is included on a field from another table. Let' say: StatusID='ASC' from StatusTable table and I have another table StatusFilterTable with Filter field which have this value: "MNR,AQC,TVR,ASC,MVR"I tried with this SP:
@status varchar(50)
AS
SELECT A.StatusID + ' - ' + A.StatusDescripcion AS StatusDescription
FROM StatusTable A
WHERE A.Activo=1 AND A.StatusID IN (SELECT B.Filter FROM StatusFilter B WHERE B.StatusID=@Status)
ORDER BY A.StatusID
RETURN
As pointed out by other posters hard to work on it without proper information but looking at the query I'm tempted to ask: Do you have any particular reason to implement it relying on an inline view strategy when a simple join appears to solve the problem?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 9, 2010 at 12:51 pm
Why are you using a WHERE IN instead of an INNER JOIN? Generally the INNER JOIN has a better performance and looks cleaner. 🙂
August 9, 2010 at 1:19 pm
blackbird (8/9/2010)
Why are you using a WHERE IN instead of an INNER JOIN? Generally the INNER JOIN has a better performance
It does?
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2010 at 8:34 pm
blackbird (8/9/2010)
Why are you using a WHERE IN instead of an INNER JOIN? Generally the INNER JOIN has a better performance and looks cleaner. 🙂
I'd post my code that shows just how wrong that statement is or refer you to Gail's outstanding blog on the subject (which she's already done), but it's someone else's turn to debuke this myth with code. I recommend YOU, blackbird. Let's see some code that absoluely proves what you said above. And, if you cannot prove it with code, please stop posting such a myth... someone might believe you.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2010 at 8:49 pm
CELKO (8/9/2010)
>> I have this problem: <<No, you have a lot of conceptual problems. You do not know the correct terms for RDBMS and SQL, or how do a data model.
>>I need to get a group of records [sic: rows are not records] where a shipment_id [no such thing] is included on a field [sic: columns are not fields] from another table.<<
It will take thousands of words to explain rows vs. recors, columns vs. fielda nd tables vs. files. But very quickly, the ISO-11179 standard for data elements tells us that an attribute can be "<something>_status" or "<something>_id" and never both.
>> Let' say: (status_id = 'ASC') from StatusTable [sic] table and I have another table StatusFilterTable with Filter field [sic] which have this value: "MNR, AQC, TVR, ASC, MVR" [sic] <<
More fundametnal problems! Why do you put the affix "Table" on a table name? Tell me what this sets of enties IS BY IT NATURE and not how you are storing it. Why did you use alphabetic order for the alias names? How does that help anyone read your code?
I hope that you at least know something about Normal Forms, so you did not really put a comma separated list in a column. Is this what you meant?
CREATE TABLE ShipmentStatusFilters
(shipment_status CHAR(3) NOT NULL PRIMARY KEY,
shipment_status_description VARCHAR(25) NOT NULL);
INSERT INTO ShipmentStatusFilters (shipment_status, shipment_status_description)
VALUES ('NR', 'Confirmation to Send'),
('AQC', 'Partener Reason'),
('TVR', 'Shipped Already'),
('ASC', 'Pending to Send'),
('MVR', 'On Hold');
I also hope that you do not write with bit flags, but that is what "active" looks like. This is an awful way to use SQL; you are back to assembly language programming.
Here is a clean up on your code fragment. Without any DDL, I had to make a lot of guesses.
CREATE PROCEDURE GetFilteredShipments (@in_shipment_status CHAR(3))
AS
SELECT S.shipment_id, S.shipment_status_description
FROM Shipments AS S
WHERE S.active_flg = 1 -- hope it is not a flag!
AND S.shipment_status
IN (SELECT F.shipment_status
FROM ShipmentStatusFilters AS F
WHERE F.shipment_id = @in_shipment_status)
ORDER BY S.shipment_id;
You do not yet understand RDBMS or SQL; you need to do more reading on the fundamentals and standards. A Forum is not the place to get this deep an education.
Heh... the OP isn't the only one with conceptional problems. Lighten up, Joe. Why do you think he's asking questions to begin with?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2010 at 11:42 pm
Guys:
I could not answer to you as I would like to, I really appreciate your comments. Since this is a 'SQL Server Newbies', I dared to ask for help. You are right, I have A LOT to learn about conceptual things, about RDMBS and such things, and now I know that I will not learn it from you.
About your requests: If I knew how to show that data and format, for sure I would send it to you. Since this is the mainly reason you could not properly analyze, I see your exasperation.
I do not give up anyway, I will continue learning about MS SQL in spite of situations like this one.
August 10, 2010 at 2:13 am
Notwithstanding the comments already made, I believe this is what the translation of your query would look like, ie trying use the IN predicate on a column value.
SELECT s.StatusID + ' - ' + s.StatusDescription AS [StatusDescription]
FROM dbo.StatusTable s
WHERE s.Activo=1 AND s.StatusID IN ('ASC','DEL','PE','PNST')
ORDER BY s.StatusID
This would give you the result you require,
SELECT s.StatusID + ' - ' + s.StatusDescription AS [StatusDescription]
FROM dbo.StatusTable s
JOIN dbo.StatusFilter f ON f.StatusID = @status AND CHARINDEX(s.StatusID,f.Filter)>0
WHERE s.Activo=1
ORDER BY s.StatusID
but this is ...
poor design
poor query
poor performance
as will be shown when Jeff catigates me for even dare to post this 😀
Far away is close at hand in the images of elsewhere.
Anon.
August 10, 2010 at 11:53 pm
Mr. Burrows:
Please receive my sincere and more than eloquent thanks. I tested your query and it worked perfectly. Since the table has 30 rows the most and are being used on configuration process only, the performance was not affected on the web service.
Since the critics I received made me think that we need to do things better, I want to change the structure of these two tables to improve them. I will start working on them as soon as possible:
TABLES:
CREATE TABLE dbo.Status
(StatusID varchar(4) NOT NULL,
StatusDescription varchar(25),
Activo bit,
PRIMARY KEY (StatusID),
UNIQUE (StatusID))
CREATE TABLE dbo.StatusFilter
(StatusID varchar(4) NOT NULL,
LinkedStatus varchar(4),
PRIMARY KEY (StatusID), (LinkedStatus),
UNIQUE (StatusID), (LinkedStatus))
DATA:
INSERT INTO dbo.Status(EstatusID,EstatusDescripcion,Activo) VALUES ('NR', 'Confirmation to Send',1),('AQC', 'Partener Reason',1),('TVR', 'Shipped Already',1),('ASC', 'Pending to Send',1),('MVR', 'On Hold',1)
INSERT INTO dbo.StatusFilter(StatusID,LinkedStatus) VALUES ('ASC','NR'),('ASC','TVR'),('ASC','ASC'),('ASC','AQC'),('ASC','MVR')
And use this query defined on a SP:
SELECT S.statusID + ' - ' + S.EstatusDescription AS Status
FROM Status S
INNER JOIN StatusFilter SF
ON S.EstatusID = SF.LinkedStatus
WHERE SF.EstatusID = @Estatus And S.Activo = 1
ORDER BY S.Status
Due to my limits on SQL, I know this is not an ideal solution, but I think is better than what I have right now.
My best regards,
🙂
PD: I did not include the FOREIGN KEYS on table creation due to I did not know how to define them in this format, but according to the SQL query defined above, there must be a relationship between S.EstatusID And SF.LinkedStatus, also between S.EstatusID And SF.StatusID.
August 11, 2010 at 3:27 am
giosermao (8/10/2010)
Please receive my sincere and more than eloquent thanks.
Your welcome.
and thank you for yours 🙂
...made me think that we need to do things better, I want to change the structure of these two tables to improve them.
All part of the learning curve 🙂
Far away is close at hand in the images of elsewhere.
Anon.
August 13, 2010 at 11:00 pm
Celko:
Thank you very much for the pretty interesting lessons in SQL. I am reading already the article you are suggesting.
My best regards,
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply