WHERE !=

Question

SimonIoa 77 Master Poster

10 Years Ago

Does anyone sees any error in this?
I tried everything, google it and still getting the same results, even when i replace the $uid with the number of the id still the same.
I have two columns friend_one and friend_two and i want to show results where neither column has the $uid
SELECT * FROM users U, friends F WHERE NOT (F.friend_two = '$uid' OR F.friend_one = '$uid') GROUP BY U.uid ORDER BY U.uid DESC

php

4 Contributors
18 Replies
311 Views
4 Days Discussion Span
Latest Post 10 Years Ago Latest Post by SimonIoa

diafol

10 Years Ago

How about this?

SELECT * FROM users U, friends F WHERE '$uid' NOT IN(F.friend_two, F.friend_one) GROUP BY U.uid ORDER BY U.uid DESC

Dani 4,310 The Queen of DaniWeb

10 Years Ago

Sorry, the only thing I can see is what diafol suggested. Perhaps using a JOIN??

urtrivedi 276 Nearly a Posting Virtuoso

10 Years Ago

To get correct result
1) I think you need to join clause to join users U, friends F
2) need to put where f.friend_two <> '$uid' and F.friend_one <> '$uid'

urtrivedi 276 Nearly a Posting Virtuoso

10 Years Ago

try following as i suggested before

where f.friend_two <> '$uid' and F.friend_one <> '$uid'

urtrivedi 276 Nearly a Posting Virtuoso

10 Years Ago

I guess problem is in your join

LEFT JOIN friends F ON U.uid=F.friend_one

You already joing uid with freind one , with that query you willl never receive result.

can you post both sample table script and sample data here or at http://sqlfiddle.com/

Edited 10 Years Ago by urtrivedi

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

SimonIoa 77 Master Poster · Answer 1 · 2014-06-27T22:33:53+00:00

sorry for the delay Still the same diafol... And the thing is that it returns me ($uid) as well as others that it doesnt suppose to

SimonIoa 77 Master Poster · Answer 2 · 2014-06-28T17:16:50+00:00

Did 2) alreadt urtrivedi, trying 1) now. Thanks

SimonIoa 77 Master Poster · Answer 3 · 2014-06-28T17:39:05+00:00

SELECT * FROM users U LEFT JOIN friends F ON U.uid=F.friend_one WHERE NOT (F.friend_two = '$uid' OR F.friend_one = '$uid') GROUP BY U.uid ORDER BY U.uid DESC

Did the join it doesn't appear me ($uid) anymore but still appears my friends. I am asking to return data that the F.friend_one or F.friend_two isnt $uid that means people that aren't friends with me ($uid)

SimonIoa 77 Master Poster · Answer 4 · 2014-06-30T11:36:42+00:00

SimonIoa 77 Master Poster

10 Years Ago

I have try it before still doesnt work

SimonIoa 77 Master Poster · Answer 5 · 2014-06-30T12:02:23+00:00

You mean this right?
SELECT * FROM users U friends F ON U.uid=F.friend_one WHERE f.friend_two <> '$uid' AND F.friend_one <> '$uid' GROUP BY U.uid

SimonIoa 77 Master Poster · Answer 6 · 2014-06-30T12:05:15+00:00

sorry this
SELECT * FROM users U, friends F WHERE f.friend_two <> '$uid' AND F.friend_one <> '$uid' GROUP BY U.uid

without the ON

SimonIoa 77 Master Poster · Answer 7 · 2014-06-30T12:07:00+00:00

With this returns me as well...
SELECT * FROM users U, friends F WHERE f.friend_two <> '$uid' AND F.friend_one <> '$uid' GROUP BY U.uid ORDER BY U.uid DESC

i tried everything

SimonIoa 77 Master Poster · Answer 8 · 2014-06-30T12:07:21+00:00

SimonIoa 77 Master Poster

10 Years Ago

by me i mean $uid

SimonIoa 77 Master Poster · Answer 9 · 2014-06-30T12:12:17+00:00

DID IT ON SQL FIDDLE RETURNS SAME RESULTS AS ME

urtrivedi 276 Nearly a Posting Virtuoso · Answer 10 · 2014-06-30T12:21:46+00:00

urtrivedi 276 Nearly a Posting Virtuoso

10 Years Ago

post your structure and sample data here

SimonIoa 77 Master Poster · Answer 11 · 2014-06-30T12:33:34+00:00

-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: May 24, 2014 at 01:06 PM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `wall`
--

-- --------------------------------------------------------


-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
  `friend_id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_one` int(11) DEFAULT NULL,
  `friend_two` int(11) DEFAULT NULL,
  `role` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`friend_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=155 ;

--
-- Dumping data for table `friends`
--

INSERT INTO `friends` (`friend_id`, `friend_one`, `friend_two`, `role`) VALUES
(1, 1, 1, 'me'),
(2, 2, 2, 'me'),
(3, 3, 3, 'me'),
(4, 4, 4, 'me'),
(5, 5, 5, 'me'),
(6, 6, 6, 'me'),
(7, 7, 7, 'me'),
(8, 8, 8, 'me'),
(9, 9, 9, 'me'),
(10, 3, 1, 'fri'),
(11, 3, 4, 'fri'),
(12, 3, 9, 'fri'),
(13, 3, 5, 'fri'),
(14, 3, 8, 'fri'),
(15, 3, 6, 'fri'),
(16, 3, 7, 'fri'),
(17, 1, 7, 'fri'),
(18, 1, 6, 'fri'),
(19, 1, 8, 'fri'),
(20, 1, 5, 'fri'),
(21, 1, 9, 'fri'),
(22, 1, 4, 'fri'),
(23, 1, 3, 'fri'),
(24, 2, 1, 'fri'),
(25, 5, 3, 'fri'),
(26, 5, 4, 'fri'),
(27, 5, 9, 'fri'),
(28, 5, 8, 'fri'),
(29, 5, 6, 'fri'),
(30, 5, 7, 'fri'),
(31, 2, 4, 'fri'),
(33, 2, 3, 'fri'),
(34, 2, 7, 'fri'),
(35, 2, 8, 'fri'),
(36, 4, 2, 'fri'),
(37, 4, 3, 'fri'),
(38, 4, 9, 'fri'),
(39, 4, 5, 'fri'),
(40, 2, 9, 'fri'),
(41, 4, 8, 'fri'),
(42, 4, 6, 'fri'),
(43, 4, 7, 'fri'),
(44, 2, 5, 'fri'),
(45, 4, 1, 'fri'),
(46, 2, 6, 'fri'),
(47, 7, 2, 'fri'),
(48, 7, 6, 'fri'),
(49, 7, 1, 'fri'),
(50, 7, 4, 'fri'),
(51, 7, 3, 'fri'),
(52, 7, 8, 'fri'),
(53, 7, 5, 'fri'),
(62, 7, 9, 'fri'),
(63, 9, 1, 'fri'),
(64, 9, 2, 'fri'),
(65, 9, 3, 'fri'),
(66, 9, 4, 'fri'),
(67, 9, 5, 'fri'),
(68, 9, 8, 'fri'),
(69, 9, 6, 'fri'),
(70, 9, 7, 'fri'),
(71, 5, 2, 'fri'),
(72, 5, 1, 'fri'),
(73, 6, 1, 'fri'),
(74, 3, 2, 'fri'),
(75, 6, 2, 'fri'),
(76, 6, 3, 'fri'),
(77, 6, 4, 'fri'),
(78, 6, 9, 'fri'),
(79, 6, 5, 'fri'),
(80, 6, 8, 'fri'),
(81, 6, 7, 'fri'),
(82, 10, 10, 'me'),
(84, 11, 11, 'me'),
(90, 10, 8, 'fri'),
(93, 2, 10, 'fri'),
(94, 12, 12, 'me'),
(96, 12, 1, 'fri'),
(97, 13, 13, 'me'),
(98, 12, 11, 'fri'),
(99, 12, 2, 'fri'),
(100, 12, 3, 'fri'),
(101, 12, 4, 'fri'),
(102, 12, 9, 'fri'),
(103, 12, 5, 'fri'),
(104, 12, 8, 'fri'),
(105, 12, 6, 'fri'),
(106, 12, 7, 'fri'),
(107, 13, 1, 'fri'),
(108, 2, 12, 'fri'),
(109, 2, 13, 'fri'),
(110, 12, 13, 'fri'),
(111, 2, 11, 'fri'),
(114, 1, 10, 'fri'),
(116, 1, 11, 'fri'),
(118, 1, 13, 'fri'),
(121, 1, 2, 'fri'),
(122, 14, 14, 'me'),
(127, 14, 2, 'fri'),
(129, 14, 4, 'fri'),
(130, 14, 7, 'fri'),
(132, 14, 6, 'fri'),
(133, 14, 9, 'fri'),
(142, 14, 1, 'fri'),
(143, 15, 15, 'me'),
(144, 15, 14, 'fri'),
(145, 15, 4, 'fri'),
(146, 15, 2, 'fri'),
(147, 15, 7, 'fri'),
(148, 15, 6, 'fri'),
(149, 15, 9, 'fri'),
(150, 15, 1, 'fri'),
(151, 14, 15, 'fri'),
(152, 16, 16, 'me'),
(153, 16, 14, 'fri'),
(154, 16, 9, 'fri');

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--



--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `profile_pic` varchar(200) DEFAULT NULL,
  `friend_count` int(11) DEFAULT '0',
  `status` int(1) DEFAULT '1',
  `name` varchar(150) DEFAULT NULL,
  `profile_pic_status` int(1) DEFAULT '0',
  `conversation_count` int(11) DEFAULT '0',
  `updates_count` int(11) DEFAULT '0',
  `first_name` varchar(200) DEFAULT NULL,
  `last_name` varchar(200) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  `location` varchar(200) DEFAULT NULL,
  `hometown` varchar(200) DEFAULT NULL,
  `bio` text,
  `relationship` varchar(30) DEFAULT NULL,
  `timezone` varchar(10) DEFAULT NULL,
  `provider` varchar(10) DEFAULT NULL,
  `provider_id` int(30) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`uid`, `username`, `password`, `email`, `profile_pic`, `friend_count`, `status`, `name`, `profile_pic_status`, `conversation_count`, `updates_count`, `first_name`, `last_name`, `gender`, `birthday`, `location`, `hometown`, `bio`, `relationship`, `timezone`, `provider`, `provider_id`) VALUES
(1, 'srinivas', '63a401a18004e5c6a5b5bd3643fbb1d5', 'srinivas@9lessons.info', '13751131401.jpg', 11, 1, 'Srinivas Tamada', 1, 0, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(2, 'rajesh', '63a401a18004e5c6a5b5bd3643fbb1d5', 'rajesht9i@gmail.com', '13743246892.png', 12, 1, 'Rajesh Tamada', 1, 2, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(3, 'dinesh', '63a401a18004e5c6a5b5bd3643fbb1d5', 'Dinesh@gmail.com', '13743002873.jpg', 8, 1, 'Dinesh Tamada', 1, 1, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(4, 'ravi', '63a401a18004e5c6a5b5bd3643fbb1d5', 'ravi8x@gmail.com', NULL, 8, 1, 'Ravi Tamada', 0, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(5, 'karthik', '63a401a18004e5c6a5b5bd3643fbb1d5', 'karthik@9lessons.info', '13743004075.jpg', 8, 1, 'Karthik ', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(6, 'arun', '63a401a18004e5c6a5b5bd3643fbb1d5', 'arun@egglabs.in', '13743006356.jpg', 8, 1, 'Arun Sekar ', 1, 0, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(7, 'ramesh', '63a401a18004e5c6a5b5bd3643fbb1d5', 'ramesh.tamada@gmail.com', '13743008297.png', 8, 1, 'Ramesh Tamada', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, 'sathish', '63a401a18004e5c6a5b5bd3643fbb1d5', 'sathish@9lessons.info', '13743012409.png', 8, 1, 'Satish', 1, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(11, 'joker', '63a401a18004e5c6a5b5bd3643fbb1d5', 'joker@9lessons.info', '137432094211.png', 0, 1, 'JOKER', 1, 2, 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(12, 'spiderman', '63a401a18004e5c6a5b5bd3643fbb1d5', 'spiderman@gmail.com', '137432125312.jpg', 11, 1, 'Spiderman', 1, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(13, 'superman', '63a401a18004e5c6a5b5bd3643fbb1d5', 'superman@9lessons.info', '137432130613.png', 1, 1, 'Man of Steel', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(14, 'simon', '0552d63459c7114fe5e2642f841374fe', 'simonioannides@hotmail.com', '137951642614.jpg', 7, 1, 'Simon', 1, 0, 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(15, 'MariosPetridis', 'b7a1c5b12419e9a678fffe1bb322c5fa', 'petridisvm@gmail.com', '139930746315.jpg', 7, 1, 'Marios Petridis', 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(16, 'john', 'e10adc3949ba59abbe56e057f20f883e', 'ipapachristoudis@gmail.com', NULL, 2, 1, NULL, 0, 0, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- --------------------------------------------------------

urtrivedi 276 Nearly a Posting Virtuoso · Answer 12 · 2014-07-01T04:35:02+00:00

SELECT * FROM `users` WHERE 
(
uid  not in (select friend_two from friends where friend_one='$uid' ) 
and
uid not in (select friend_one from friends where friend_two='$uid' ) 
)

SimonIoa 77 Master Poster · Answer 13 · 2014-07-01T11:41:51+00:00

SimonIoa 77 Master Poster

10 Years Ago

thanks man thats it...