1

Sorry I don't really know how to make a title for this because I can't explain it really. for example i have here a table

c_id   emp_id   clinic_id
1      1         1
2      1         2
3      2         1
4      3         3
5      1         3

now i will do a query like this

select distinct * from table where clinic_id <> 1

And the result would be

c_id   emp_id   clinic_id
2      1         2
4      3         3
5      1         3

at this point I need help, if from the where clinic_id <> 1 a certain emp_id is within its row of condition(Sorry for my bad english). for example emp_id 1. All emp_id 1 must not be display also. So the result would be just

c_id   emp_id   clinic_id
4      3         3 // *The result I want*
Noobster
  • 93
  • 11
  • you either accept the results in the order you get them from in the db, or you impose your own ordering via an `order by` clause. you CAN have your client code simply suck in all the rows and then spit them out last-first, but that's not an sql problem. – Marc B Jul 04 '16 at 17:37
  • Edit. It is more than just that data. `order by` doesn't help – Noobster Jul 04 '16 at 17:40
  • 1
    In a normalised environment `DISTINCT *` is oxymoronic – Strawberry Jul 04 '16 at 22:56

3 Answers3

1

You can use NOT EXISTS for this:

select distinct * 
from mytable as t1
where clinic_id <> 1 and 
      not exists (select 1
                  from mytable as t2
                  where t1.emp_id = t2.emp_id and t2.clinic_id = 1)
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
0

For the result you're looking for, wouldn't something like this be simpler?

SELECT DISTINCT * FROM table WHERE clinic_id !=1 AND emp_id !=1

Here we're saying we want any clinic_id that is not 1 and any emp_id that is also not 1.

Since you're dealing with PHP, then you would simply substitute the numbers for the variables you're trying to not match.

SELECT DISTINCT * FROM table WHERE clinic_id !=$session_variable AND emp_id !=$some_value

AbsoluteƵERØ
  • 7,816
  • 2
  • 24
  • 35
  • 1
    Im coding with PHP and using session for `clinic_id` so I cant put a absolute value in the `emp_id` :) – Noobster Jul 04 '16 at 17:57
-1
SELECT DISTINCT * 
FROM table
WHERE clinic_id <> 1 
AND emp_id NOT IN 
    (SELECT DISTINCT emp_id 
            FROM table
            WHERE clinic_id = 1)

Try this one.

It uses the subquery to return the emp_ids which are in the same row as the 1 in the column clinic_id, and removes them from the resultset because you also don't want those emo_ids.

Also you could use a GROUP BY clause instead of DISTINCT. Usually GROUP BY would be turned into a distinct by the database if you are not using any aggregate functions, but sometimes they behave differently. If you are interested in this topic you can also see this question: Is there any difference between GROUP BY and DISTINCT

SELECT c_id, emp_id, clinic_id 
FROM clinics
WHERE clinic_id <> 1 
AND emp_id NOT IN 
    (SELECT DISTINCT emp_id 
            FROM clinics
            WHERE clinic_id = 1)
GROUP BY c_id, emp_id, clinic_id;
Community
  • 1
  • 1
Philipp
  • 2,787
  • 2
  • 25
  • 27
  • Yes, i can see the result and it work just fine. just 1 problem though, I have long stack of data here. and some clinic_id is duplicating – Noobster Jul 04 '16 at 17:55
  • I don't see the problem tbh. Do you mean you have to check multiple clinic_ids at once? Then you could use `clinic_id NOT IN(clinid_id1, clinic_id2,...)` in the where clauses of the main and subquery – Philipp Jul 04 '16 at 18:04
  • DISTINCT * is an oxymoron – Strawberry Jul 04 '16 at 22:54
  • Well, I think it communicates more clearly what the op wants to achieve than a group by. This is also the first time I heard that DISTINCT * is a bad thing, an explanation or an article/blogpost/etc. about this is much appreciated. [removed link to wrong dbms] – Philipp Jul 05 '16 at 05:40
  • Please, mr. @Strawberry enlighten us. – Noobster Jul 06 '16 at 13:10