Link to home
Start Free TrialLog in
Avatar of Oliver2000
Oliver2000Flag for Brazil

asked on

mysql select but exclude already some cases

Hello everybody,

I need some tip for mysql select where I dont know what to do.

I do have a table with rows like this

POST | NAME | FOTO | USER_ID | SENDER_ID | TEXT |

Now I want to select DESTINCT all results but not the ones where SENDER_ID = 1000 AND USER_ID NOT 2000

but only in this combination. For example I want all with SENDER_ID = 1000 but USER_ID =! 1000 and the opposite but not IF BOTH are the the numbers I want to exclude.

I could of course first select all and than via php exclude this ones with a simple if but then I get less results. If I select for example with limit 10 and later I check with php if to exclude maybe 2-3 of the result I would end up with only 7 rows to display which would be strange for the layout. So I try to exclude them already during the select if possible.

I hope I did explain it well enough and somebody can help me on the right way.

thank you for all tips
Avatar of Pratima
Pratima
Flag of India image

Not sure I got your question correctly
but as per my understanding you don't want to fetch the row with SENDER_ID = 1000 but USER_ID =! 1000

then try this

select * from tablename
where NOT EXISTS (
select * from tablename where SENDER_ID = 1000 and USER_ID =! 1000 )
Avatar of plitero2
plitero2

I confess that I have a hard time understanding your issue as you describe it, but here's a try.  The previous example of a nested SELECT statement seems unnecessary.

Why not go?:

SELECT * FROM tablename WHERE (SENDER_ID <> 1000 AND USER_ID <> 1000)

That statement will only exclude the values you wish it to.  If I misunderstood whether you wanted them equal or NOT equal, you could just change the corresponding value to "=".
@Oliver2000: An example showing the inputs and expected outputs would be very helpful here.  MySQL is a very robust data base and the SELECT command is very well understood, so if you can show us what you want to get from a sample table I am sure we can give you the exact query.  I would also like to recommend this book.  Great examples, very readable and informative.
http://www.sitepoint.com/books/phpmysql4/

If you want to learn a little more about how to use the WHERE clause in the SELECT command, this link will tell you all you want and more!
http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

Best regards, ~Ray
Avatar of Oliver2000

ASKER

Hi everybody, thanks for your anwser so far. The problem is that it is really not easy to explain. I try to explain better the situation.

I do have a wall for my users (like facebook) and users have also a newsfeed with all the events who happened at the wall of their friends. This works all fine. But I post also on the wall of the users some automatic notifications for new friendships, image uploads etc.

This function post at all the walls of the friends from this user a message and notify them if for example a picture was uploaded.

The select which I need to change does original everything fine. It selects from all the friends walls what happened on their walls and display the results for the user. How ever I DONT WANT that this select also display (or select) the entrys which are made by the automatic robot if they are NOT for the user personal. If I dont change the current select the result shows for example if a FRIEND of a friend posted a new image because the robot script wrote this at the wall of the friend. But this information I dont want to get because it was not for me but for my friend only. But the robot posts also sometime things for me so I cant exlcude him complete. I want only to exclude from the select entries which are from the robots_user_id and NOT for my own user_id.

The original select looks like this:

SELECT DISTINCT facebook_posts.p_id, facebook_posts.userid, facebook_posts.posted_by, facebook_posts.post, phpbb_users . * , UNIX_TIMESTAMP( ) - facebook_posts.date_created AS TimeSpent, facebook_posts.date_created
FROM facebook_posts, phpbb_users
WHERE (
phpbb_users.user_id
IN (

SELECT contact_id
FROM phpbb_contact_list
WHERE user_id =7314
AND user_ignore =0
AND disallow =0
)
OR phpbb_users.user_id =7314
)
AND phpbb_users.user_id = facebook_posts.userid
GROUP BY facebook_posts.post
ORDER BY `facebook_posts`.`p_id` DESC
LIMIT 0 , 10 

Open in new window


In this case 7314 is my (from the newsfeeds owner ID) The select checks first in contact_table who are my friends and after this selects from the post_table all the posts of my friends walls.

The result shows currently something like this:

p_id         /   userid    /    posted_by   /       etc.  ***********************
001           324              2323
002           7314            100
003           657              100
004           321              302
005           322              1254
etc.

the robots user_id is 100 and if we take a look in the result he posted twice. Once id 002 and id 003 but only id 002 was for user 7314 the other one I DONT WANT!

So I need in this example ALL the results but not 003 because this was the robot and NOT for me.

I hope this helps to understand my problem.

Thank you all for your patients and help.
Nobody with a tip for me?
An example showing the inputs and expected outputs would be very helpful here.  We do not really need to see code that does not work.  If you can post the CREATE TABLE statements and show us what is in the tables and what you want to get back from the query, that would help us get you an answer.  Thanks, ~Ray
Hi Ray, I am not sure if I understand you with this, i thought my example before explained it very well. But lets see i I understand you..

This would be the table:

CREATE TABLE IF NOT EXISTS `facebook_posts` (
  `p_id` int(11) NOT NULL auto_increment,
  `post` text NOT NULL,
  `type` varchar(50) NOT NULL COMMENT 'link OR status',
  `date_created` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `posted_by` int(11) NOT NULL,
  PRIMARY KEY  (`p_id`),
  KEY `userid` (`userid`),
  KEY `posted_by` (`posted_by`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=594 ;

In the table are things like this:
INSERT INTO `facebook_posts` (`p_id`, `post`, `type`, `date_created`, `userid`, `posted_by`) VALUES
(1, 'Hi everybody....', '', 1302125334, 53762, 53762),

I want to do a select on this table to get all results IF the entry was for userid (001,002,003,004 etc.) but not if the posted_by is 100 and the user_id != 001. but for example if the posted_by = 100 and user_id = 002.

I hope this was better to understand.

thankx
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Ray,

Thanks for your help, it actually does work like this but I got a massive performance problem and if I change the select like this the select takes suddenly forever. I decided to split this whole thing in separated selects and later sort and filter the array.

I appreciate your help, I did not know that I can work like this with OR and ().

cya
Oliver
If that query is not running fast enough, you might want to check the indexes on your facebook_posts table.  Every column used in a WHERE, ORDER BY, GROUP BY clause should be indexed.

Also, learn about EXPLAIN SELECT - if your queries get more complex, it is sometimes good to see how MySQL is interpreting them for execution.

Thanks for the points, ~Ray
Thankx for your tips Ray. I am going to check this all next week with more details. Have a nice weekend.
You, too.  All the best, and thanks for using EE. ~Ray