CODEWITHSUNDEEP
phpmysqlsqlselect
jmishra
jmishra

Reputation: 2081

Selecting values present in one column but not in all other columns

Suppose I have columns col1, col2, col3, col4 in myTable and I need to print out the values exclusive to only one column .

So if the above looks like 

  col1    col2    col3    col4
  s        e       c        b 
  c        c       a        s
  n        s       e        a
  d        d       q        c

Then the output should be n, q b since they are exclusive only to col1, col3 and col4 respectively.

How can I achieve this through a query in mysql php?

EDIT The duplicates dont have to be in a single row .I have changed the the table layout now to make it clear.

Upvotes: 2

Views: 392

Answers (4)

Pratik
Pratik

Reputation: 1

Try This:

SELECT col1 AS unique_on_col1
FROM table
WHERE col1 NOT IN (SELECT col2 FROM table)
  AND col1 NOT IN (SELECT col3 FROM table)
  AND col1 NOT IN (SELECT col4 FROM table)

Upvotes: 0

bfavaretto
bfavaretto

Reputation: 71939

If you are looking for a SQL-only solution, you can do a query per column like this:

SELECT 
    col1 AS unique_on_col1
FROM table
WHERE     col1 NOT IN (SELECT col2 FROM table)
      AND col1 NOT IN (SELECT col3 FROM table)
      AND col1 NOT IN (SELECT col4 FROM table)

It's possible to combine all four queries with UNION but that may not be necessary depending on what you want to do with the data. Also, this query should not perform very well with large datasets.

Upvotes: 1

Mark Reed
Mark Reed

Reputation: 95315

One slightly more compact way of getting all of them at once:

select distinct col1 
  from myTable 
 where col1 not in (select a.col1 
                     from myTable a join myTable b 
                       on a.col1 = b.col2 or a.col1=b.col3 or a.col1=b.col4)
 union
select distinct col2 
  from myTable 
 where col2 not in (select a.col2 
                     from myTable a join myTable b 
                       on a.col2 = b.col1 or a.col2=b.col3 or a.col2=b.col4)
 union
select distinct col3 
  from myTable 
 where col3 not in (select a.col3 
                     from myTable a join myTable b 
                       on a.col3 = b.col1 or a.col3=b.col2 or a.col3=b.col4)
 union
select distinct col4 
  from myTable 
 where col4 not in (select a.col4 
                     from myTable a join myTable b 
                       on a.col4 = b.col1 or a.col4=b.col2 or a.col4=b.col3)

Upvotes: 1

Naveen
Naveen

Reputation: 687

$sql = "
    SELECT DISTINCT 'col1' as descr,col1 as val FROM myTable
    UNION
    SELECT DISTINCT 'col2' as descr,col2 as val FROM myTable
    UNION
    SELECT DISTINCT 'col3' as descr,year as val FROM myTable";

$result = @mysql_query($sql, $con) or die(mysql_error());

while($row = mysql_fetch_array($result)) {
    $make_array[$row['descr']][]=$row['val'];
}

I'm guessing this should work. Dint try the code out but give this one a shot and let us know.

Upvotes: 0

Related Questions