February 16, 2017 at 1:47 pm
Hi,
Sample data to play with
Declare @vendor table(IdVenor int,MessageId int);
Declare @vendor_customer table(IdVenorCustom int,MessageId int);
Declare @Message table(MessageId int, description varchar(4000));
insert into @Message(MessageId,description)
select 100,'sample1' union all
select 200,'sample2' union all
select 200,'sample3' union all
select 400,'sample4' union all
select 500,'sample4'
insert into @vendor(IdVenor,MessageId)
select 1,100 union all
select 1,200 union all
select 1,300 union all
select 1,400
insert into @vendor_customer(IdVenorCustom,MessageId)
select 1,100 union all
select 1,200 union all
select 1,300 union all
select 1,400 union all
select 1,500
select * from @Message M where (exists(select 1 from @vendor where MessageId = M.MessageId ) or
exists(select 1 from @vendor_customer where MessageId = M.MessageId ) )
The logic i am looking for is, if any data exists in @vendor just bring those else bring from @vendor_custom. since i am using or condition it passes both the condition.
Any suggestion how to fix this?
[Note: i can write if else condition to achieve this.]. But i need to fix this with my above query. Any suggestion please
Expected result :
Since data exists from @vendor table, i need to get those results.
MessageId description
100 sample1
200 sample2
200 sample3
400 sample4
February 16, 2017 at 2:06 pm
Try this on for size. It just tests for existence a slightly different way, and uses UNION ALL to join the two possible sets of data. Only one will ever have data in it.
DECLARE @vendor AS TABLE (
IdVenor int,
MessageId int
);
DECLARE @vendor_customer AS TABLE (
IdVenorCustom int,
MessageId int
);
DECLARE @Message AS TABLE (
MessageId int,
description varchar(4000)
);
INSERT INTO @Message(MessageId,description)
SELECT 100,'sample1' UNION ALL
SELECT 200,'sample2' UNION ALL
SELECT 200,'sample3' UNION ALL
SELECT 400,'sample4' UNION ALL
SELECT 500,'sample4'
INSERT INTO @vendor(IdVenor,MessageId)
SELECT 1,100 UNION ALL
SELECT 1,200 UNION ALL
SELECT 1,300 UNION ALL
SELECT 1,400
INSERT INTO @vendor_customer(IdVenorCustom,MessageId)
SELECT 1,100 UNION ALL
SELECT 1,200 UNION ALL
SELECT 1,300 UNION ALL
SELECT 1,400 UNION ALL
SELECT 1,500;
SELECT M.*
FROM @Message AS M
WHERE EXISTS (SELECT 1 FROM @vendor AS V WHERE V.MessageId = M.MessageId)
UNION ALL
SELECT M.*
FROM @Message AS M
WHERE NOT EXISTS (SELECT 1 FROM @vendor AS V WHERE V.MessageId = M.MessageId)
AND EXISTS (SELECT 1 FROM @vendor_customer AS VC WHERE VC.MessageId = M.MessageId);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 2:20 pm
Hi Steve,
thanks for the reply and it's not producing my actual result. Since the data exists on the @vendor, we should not pull any data from @vendor_custom. so logically only 4 rows should be pulled. but your query give the extra record from @vendor_custom table.
February 16, 2017 at 2:25 pm
What do you want to happen if there is information in the @vendor table, but it doesn't match any of the records in the @Message table? No rows? Records that match the @vendor_customer table?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 16, 2017 at 2:34 pm
MessageId is foreign key in the @vendor and @vendor_custom table. so there is no way that data will be in @vendor table and not in @ message table. i didn't put the relation db structure on the sample temp variable .
The logic here is, get the data from @vedor table if exists else check for @vendor_cutome table. else return nothing.
February 16, 2017 at 2:48 pm
;
WITH CTE AS
(
SELECT *, DENSE_RANK() OVER(ORDER BY n) AS dr
FROM @Message m
CROSS APPLY (
SELECT 1
FROM @vendor v
WHERE v.MessageId = m.MessageId
UNION ALL
SELECT 2
FROM @vendor_customer vc
WHERE vc.MessageId = m.MessageId
) t(n)
)
SELECT MessageId, description
FROM CTE
WHERE CTE.dr = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 17, 2017 at 6:49 am
thank you allen.
February 17, 2017 at 11:55 am
Not sure just how much the real-world performance will be impacted, but the number of reads for my amended version of my query are just slightly less, as are the number of scans. Here's both Drew's and my query in the same stream, so that you can see the statistics side by side:
SET NOCOUNT ON;
DECLARE @vendor AS TABLE (
IdVenor int,
MessageId int
);
DECLARE @vendor_customer AS TABLE (
IdVenorCustom int,
MessageId int
);
DECLARE @Message AS TABLE (
MessageId int,
description varchar(4000)
);
INSERT INTO @Message(MessageId,description)
SELECT 100,'sample1' UNION ALL
SELECT 200,'sample2' UNION ALL
SELECT 200,'sample3' UNION ALL
SELECT 400,'sample4' UNION ALL
SELECT 500,'sample4'
INSERT INTO @vendor(IdVenor,MessageId)
SELECT 1,100 UNION ALL
SELECT 1,200 UNION ALL
SELECT 1,300 UNION ALL
SELECT 1,400
INSERT INTO @vendor_customer(IdVenorCustom,MessageId)
SELECT 1,100 UNION ALL
SELECT 1,200 UNION ALL
SELECT 1,300 UNION ALL
SELECT 1,400 UNION ALL
SELECT 1,500;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- DREW's QUERY
WITH CTE AS
(
SELECT *, DENSE_RANK() OVER(ORDER BY n) AS dr
FROM @Message m
CROSS APPLY (
SELECT 1
FROM @vendor v
WHERE v.MessageId = m.MessageId
UNION ALL
SELECT 2
FROM @vendor_customer vc
WHERE vc.MessageId = m.MessageId
) t(n)
)
SELECT MessageId, description
FROM CTE
WHERE CTE.dr = 1;
--STEVE's QUERY
SELECT M.*
FROM @Message AS M
WHERE EXISTS (SELECT 1 FROM @vendor AS V WHERE V.MessageId = M.MessageId)
UNION ALL
SELECT M.*
FROM @Message AS M
WHERE NOT EXISTS (SELECT 1 FROM @vendor AS V WHERE V.MessageId IN (SELECT MessageId FROM @Message))
AND EXISTS (SELECT 1 FROM @vendor_customer AS VC WHERE VC.MessageId = M.MessageId);
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 20, 2017 at 12:55 pm
As a complete aside, here's a tip I learned from Kendra Little. These two commands from Steve's script:SET STATISTICS IO ON;
SET STATISTICS TIME ON;
Can be combined into one:SET STATISTICS IO, TIME ON;
February 21, 2017 at 2:55 pm
KGJ-Dev - Thursday, February 16, 2017 1:47 PMHi,Sample data to play with
Declare @vendor table(IdVenor int,MessageId int);
Declare @vendor_customer table(IdVenorCustom int,MessageId int);
Declare @Message table(MessageId int, description varchar(4000));insert into @Message(MessageId,description)
select 100,'sample1' union all
select 200,'sample2' union all
select 200,'sample3' union all
select 400,'sample4' union all
select 500,'sample4'insert into @vendor(IdVenor,MessageId)
select 1,100 union all
select 1,200 union all
select 1,300 union all
select 1,400insert into @vendor_customer(IdVenorCustom,MessageId)
select 1,100 union all
select 1,200 union all
select 1,300 union all
select 1,400 union all
select 1,500select * from @Message M where (exists(select 1 from @vendor where MessageId = M.MessageId ) or
exists(select 1 from @vendor_customer where MessageId = M.MessageId ) )The logic i am looking for is, if any data exists in @vendor just bring those else bring from @vendor_custom. since i am using or condition it passes both the condition.
Any suggestion how to fix this?
[Note: i can write if else condition to achieve this.]. But i need to fix this with my above query. Any suggestion pleaseExpected result :
Since data exists from @vendor table, i need to get those results.
MessageId description
100 sample1
200 sample2
200 sample3
400 sample4
Canyou take the time to learn how to post valid DDL and how RDBMS works?Tables model sets; therefore there names have to be plural orcollective nouns. Since you do not do math on identifiers, they cannever be integers. Basically your posting a linked list from the 1950assembly language programs, but you are using SQL to do it.
Didyou know that by definition, a table has to have a key? But there isno way your samples can have a key. Why are not you using the correctsyntax for the insertion statements?
Ihave seen some of your other postings. And frankly, I would neveremploy you or let you graduate from a class of mine. You really donot understand what is going on.
CREATETABLE Vendors
(vendor_idCHAR(10) NOT NULL PRIMARY KEY);
CREATETABLE Something_Messages
(message_nbrINTEGER NOT NULL PRIMARY KEY,
message_txtNVARCHAR(4000) NOT NULL);
CREATETABLE Customer_Messages
(vendor_idCHAR(10) NOT NULL
REFERENCESVendors (vendor_id)
ONDELETE CASCADE,
message_nbrINTEGER NOT NULL
REFERENCESSomething_Messages(message_nbr)
ONDELETE CASCADE,
PRIMARYKEY (vendor_id, message_nbr)
);
Whydid you use the old Sybase syntax for your insertion statement? Itwas replaced decades ago by the row constructor syntax which isANSI/ISO standard?
>>The logic I am looking for is, if any data exists in Vendors justbring those else bring from Vendors_custom. since I am using orcondition it passes both the condition. <<
thanksto referential integrity, this request makes no sense at all. Yourcode is more than a decade out of date.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 22, 2017 at 2:05 am
jcelko212 32090 - Tuesday, February 21, 2017 2:55 PMWhydid you use the old Sybase syntax for your insertion statement? Itwas replaced decades ago by the row constructor syntax which isANSI/ISO standard?
Why? Because if you had read the article on how to post sample data on these forums, you would see that that it how it recommends you generate it. I would be very surprised if the OP used this format in production, but for the purposes of providing sample data, so that an online community can help them solve there problem, why is it a problem. Answer: it's not.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 22, 2017 at 2:36 am
Thom A - Wednesday, February 22, 2017 2:05 AMjcelko212 32090 - Tuesday, February 21, 2017 2:55 PMWhydid you use the old Sybase syntax for your insertion statement? Itwas replaced decades ago by the row constructor syntax which isANSI/ISO standard?Why? Because if you had read the article on how to post sample data on these forums, you would see that that it how it recommends you generate it. I would be very surprised if the OP used this format in production, but for the purposes of providing sample data, so that an online community can help them solve there problem, why is it a problem. Answer: it's not.
Because, just like starting a statement with a semi-colon, which some people also get upset about, it's legal and it's effective. And it works with all versions. It may not be the way you or I would choose to write code, but that's neither here nor there. And I think when you say "decades", you mean nine years, since that's how long ago the row constructor syntax was added to T-SQL.
John
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply