How to hide specific record(s) from sql results?
-
Hi Guys,
I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"
What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.
I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")
SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())
Any ideas
Thanks
-
Hopefully you have something that is joins both tables together, customer ID, that is unique to a particular customer.
create table #customer
(id int, name varchar(10))
create table #logs
(custid int, checked datetime)
insert into #customer values
(1,'cust1'),
(2,'cust2'),
(3,'cust3')
insert into #logs values
(1,'2015-11-01'),
(2,'2015-10-01'),
(3,'2015-01-01')
select
*
from
#customer c
inner join
#logs l
on
c.id = l.custid
where
l.checked >= DATEADD(day,-90,getdate())
drop table #customer, #logs
-
Thanks for the relpy.It works but can you check this illustration.This is what I need to implement
-
This perhaps?
select
*
from
customer c
WHERE NOT EXISTS
(
SELECT*
FROMlogs l
WHERE l.custid = c.id
AND l.checked >= DATEADD(day,-90,getdate())
)
-
Is this what you need?
create table #customer
(id int, name varchar(10))
create table #logs
(custid int, checked datetime)
insert into #customer values
(1,'cust1'),
(2,'cust2'),
(3,'cust3')
insert into #logs values
(1,'2015-11-01'),
(2,'2015-10-01'),
(3,'2015-01-01')
select
*
from
#customer c
left join
#logs l
on
c.id = l.custid and l.checked <= DATEADD(day,-90,getdate())
Also please take a moment to read through the links in my signature on posting code and data for the best help. Screenshots are not as ideal as you may think.
-
shiraj (11/2/2015)
Hi Guys,I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"
What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.
I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")
SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())
Any ideas
Thanks
Just change your inner join to a left join. You should take a look at this article which explains joins. http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/[/url]
_______________________________________________________________
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/ -
Thank you 🙂 it worked as a charm.Thanks guys for your replies
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply