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

Recommended Answers

All 18 Replies

Member Avatar for diafol

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

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

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

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'

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

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)

try following as i suggested before

where f.friend_two <> '$uid' and F.friend_one <> '$uid'
I have try it before still doesnt work

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/

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

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

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

by me i mean $uid

DID IT ON SQL FIDDLE RETURNS SAME RESULTS AS ME

post your structure and sample data here

-- 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);

-- --------------------------------------------------------
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' ) 
)

thanks man thats it...

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.