only want one record returned per card (was "Query Help - New expert-NOT")

I only want one record returned PER card that has access to either of the doors specified in readername. My current results ARE below query

CURRENT QUERY:

Select lastname, cards.cardid,firstname,readername
from Cards, cardaccesslevels, readers
where (readername = 'Door 156B-RF Vest to Prod RF-N'
or readername = 'Door 157a-RF Vest to Prod RF - S'
or readername = 'Door 122B-WarRoom to RF'
or readername = 'Door 121-ecc to raised floor'
or readername = 'Door 154a-Hallway to UPS'
or readername = 'Door 131A-Battery Room A'
or readername = 'Door 151b-switchgear exit'
or readername = 'Door 132-battery room b'
or readername = 'Door 140B-Rec to chiller room'
or readername = 'Door 130-Hallway to Telco vendr b'
or readername = 'Door 134-hallway to telco vendr a')
and (cards.cardid = cardaccesslevels.cardid)
order by lastname
****
CURRENT RESULTS
Abdullah 497 Phakhruddin Door 121-ECC to Raised Floor
Abdullah 497 Phakhruddin Door 131A-Battery Room A
Abdullah 497 Phakhruddin Door 132-Battery Room B
Abdullah 497 Phakhruddin Door 140B-Rec to Chiller room
Abdullah 497 Phakhruddin Door 154A-Hallway to UPS
Abdullah 497 Phakhruddin Door 156B-RF Vest to Prod RF-NHow about:
Select DISTINCT lastname, cards.cardid,firstname
from Cards, cardaccesslevels, readers
where (readername = 'Door 156B-RF Vest to Prod RF-N'
or readername = 'Door 157a-RF Vest to Prod RF - S'
or readername = 'Door 122B-WarRoom to RF'
or readername = 'Door 121-ecc to raised floor'
or readername = 'Door 154a-Hallway to UPS'
or readername = 'Door 131A-Battery Room A'
or readername = 'Door 151b-switchgear exit'
or readername = 'Door 132-battery room b'
or readername = 'Door 140B-Rec to chiller room'
or readername = 'Door 130-Hallway to Telco vendr b'
or readername = 'Door 134-hallway to telco vendr a')
and (cards.cardid = cardaccesslevels.cardid)
order by lastname|||I appreicate your help. It did work. I was thrust into an admin role (my boss tells everyone I am an expert..ha) for a database and my sql experience is limited which makes my job painful right now.

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay|||Absolutely right My mistake
I have updated my Query and if i run the below Query it Gives ame an Error as
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Update JAX_EOL_COntract
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
please advice
Thanks
"Aaron [SQL Server MVP]" wrote:
> What is the purpose of Contract_ID in the inner SELECT?
> Look at the query you're running. You're saying, basically, "... SET
> some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
> the SET statement supposed to do with it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> >
> > Update COntract
> > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > FROM LinkedServer.Closed_Contract
> > where Disp_Date is not Null)
> >
> > This Query gives me an Error as
> > Only one expression can be specified in the select list when the subquery
> is
> > not introduced with EXISTS.
> >
> > My Select Query does return more then 1 record. is their any way to
> perform
> > this Query in a better way as the select Query retrieves more then 200,000
> > records.
> >
> > LinkedServer = Linked server from one Server to another
> >
> > Thanks
> > samay
>
>|||Well if there are two dates in Closed_Contract where disp_date is not null,
let's say 20041110 and 20041108, which one do you want? You can use SELECT
MIN() or SELECT MAX() or some other condition which enforces only one row to
be returned.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
> Absolutely right My mistake
> I have updated my Query and if i run the below Query it Gives ame an Error
as
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The statement has been terminated.
> Update JAX_EOL_COntract
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> please advice
> Thanks
> "Aaron [SQL Server MVP]" wrote:
> > What is the purpose of Contract_ID in the inner SELECT?
> >
> > Look at the query you're running. You're saying, basically, "... SET
> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
is
> > the SET statement supposed to do with it?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> > >
> > > Update COntract
> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > > FROM LinkedServer.Closed_Contract
> > > where Disp_Date is not Null)
> > >
> > > This Query gives me an Error as
> > > Only one expression can be specified in the select list when the
subquery
> > is
> > > not introduced with EXISTS.
> > >
> > > My Select Query does return more then 1 record. is their any way to
> > perform
> > > this Query in a better way as the select Query retrieves more then
200,000
> > > records.
> > >
> > > LinkedServer = Linked server from one Server to another
> > >
> > > Thanks
> > > samay
> >
> >
> >|||Following up on Aaron's comment, possibly you need to add a restriction to
the subquery so it only returns the record for the current contract.
Something like
Update JAX_EOL_COntract JC
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract CC
where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Mike A.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:echAR10xEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Well if there are two dates in Closed_Contract where disp_date is not
> null,
> let's say 20041110 and 20041108, which one do you want? You can use
> SELECT
> MIN() or SELECT MAX() or some other condition which enforces only one row
> to
> be returned.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message
> news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
>> Absolutely right My mistake
>> I have updated my Query and if i run the below Query it Gives ame an
>> Error
> as
>> Subquery returned more than 1 value. This is not permitted when the
> subquery
>> follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
>> The statement has been terminated.
>> Update JAX_EOL_COntract
>> Set dDispositionDate = (SELECT Disp_Date
>> FROM LinkedServer.Closed_Contract
>> where Disp_Date is not Null)
>> please advice
>> Thanks
>> "Aaron [SQL Server MVP]" wrote:
>> > What is the purpose of Contract_ID in the inner SELECT?
>> >
>> > Look at the query you're running. You're saying, basically, "... SET
>> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
> is
>> > the SET statement supposed to do with it?
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>> >
>> >
>> > "KritiVerma@.hotmail.com"
> <KritiVermahotmailcom@.discussions.microsoft.com>
>> > wrote in message
> news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
>> > >
>> > > Update COntract
>> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
>> > > FROM LinkedServer.Closed_Contract
>> > > where Disp_Date is not Null)
>> > >
>> > > This Query gives me an Error as
>> > > Only one expression can be specified in the select list when the
> subquery
>> > is
>> > > not introduced with EXISTS.
>> > >
>> > > My Select Query does return more then 1 record. is their any way to
>> > perform
>> > > this Query in a better way as the select Query retrieves more then
> 200,000
>> > > records.
>> > >
>> > > LinkedServer = Linked server from one Server to another
>> > >
>> > > Thanks
>> > > samay
>> >
>> >
>> >
>|||> Following up on Aaron's comment, possibly you need to add a restriction to
> the subquery so it only returns the record for the current contract.
> Something like
> Update JAX_EOL_COntract JC
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract CC
> where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Yes, this will work, assuming a one-to-one relationship (which, based on
table names, seems likely).

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samay
What is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay