• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

MySql Query, Select distinct where... except

I have:

"SELECT DISTINCT firstName,lastName FROM tbl_users WHERE active = 'yes';"

Open in new window


I want to add an exception.... how can I block someone from appearing?
0
cstormer
Asked:
cstormer
3 Solutions
 
cyberkiwiCommented:
SELECT DISTINCT firstName,lastName
FROM tbl_users
WHERE active = 'yes'
and not (firstname='$firstname' and lastname='$lastname')

or if you know the ID

SELECT DISTINCT firstName,lastName
FROM tbl_users
WHERE active = 'yes' and id <> $id
0
 
Rajesh DalmiaCommented:
can u explain  more
is it any specific member you want to block or specific type or anything else.
0
 
bonjourjolieCommented:
by which field do you want to block ?
by firstName ? or .... ?

for example

"SELECT DISTINCT firstName,lastName FROM tbl_users WHERE active = 'yes' and firstName <> 'Blocked user';"

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dfendigCommented:
You can add additional logic to the end of your SQL Statement. For example:

      

"SELECT DISTINCT firstName,lastName FROM tbl_users WHERE active = 'yes' AND firstname !='firstnametoexclude';"
or
"SELECT DISTINCT firstName,lastName FROM tbl_users WHERE active = 'yes' AND lastname !='lastnametoexclude';"



0
 
Aaron TomoskyTechnology ConsultantCommented:
And if you want to block a whole group of users:
Where active = 'yes' and firstname not in ('a', 'b')

Or:
firstname not like 'a%'

Or:
firstname not in (select firstname from table where state = 'ca')
0
 
cyberkiwiCommented:
> I want to add an exception.... how can I block someone from appearing?

Surely you need both first and last name, otherwise you're blocking Joe bloggs, Joe public and Joe white just by using

"SELECT DISTINCT firstName,lastName FROM tbl_users WHERE active = 'yes' and firstName <> 'Joe';"

What's wrong with the first comment?

SELECT DISTINCT firstName,lastName
FROM tbl_users
WHERE active = 'yes'
and not (firstname='$firstname' and lastname='$lastname')

e.g. if your php var is $firstname='Joe' and $lastname='Public', then the query

WHERE active = 'yes' and not (firstname='Joe' and lastname='public')

only removes joe public and leaves all other joes. The name is matched on both first+last name.
If it is possible to have similarly named people (John Brown is common), I suggested using the ID if you have it available.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now