Wednesday, March 7, 2012

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.

No comments:

Post a Comment