CODEWITHSUNDEEP
phpmysqlsql
Amardeepvijay
Amardeepvijay

Reputation: 1628

How to get unique data from a table?

I have a table which has bus_id and cus_id in mysql. I want to get B108 as a return value from the table because I want result whose value is not equal to C108 and C108 has B109 also. So I do not want row 3 value also. I want to get only row 2 as my result. Please suggest me the select query for these table.

My table is :

enter image description here

Upvotes: 0

Views: 85

Answers (6)

Krofz
Krofz

Reputation: 111

If you mean ignore record who have 2 or more data in column bus_id or cus_id, maybe you can use this query : 

select * from buscus a where
   a.bus_id not in (select b.bus_id from (select count(bus_id) as cb, bus_id FROM BusCus group by bus_id) b where    b.cb > 1) 
and
   a.cus_id not in (select c.cus_id from (select count(cus_id) as cc, cus_id FROM BusCus group by cus_id) c where c.cc >    1)

.

Upvotes: 0

Satheesh Variath
Satheesh Variath

Reputation: 680

Try this query

CREATE TABLE BUS
    (`cb_id` int, `bus_id` varchar(4), `cus_id` varchar(4))
;

INSERT INTO BUS
    (`cb_id`, `bus_id`, `cus_id`)
VALUES
    (1, 'B101', 'C108'),
    (2, 'B108', 'C101'),
    (3, 'B109', 'C102'),
    (24, 'B109', 'C108')
;




SELECT
    * 
FROM BUS B
WHERE cus_id<>'C108'
AND NOT EXISTS(SELECT * FROM BUS B1 
        WHERE 
             B1.BUS_ID=B.BUS_ID 
         AND B1.cus_id='C108')

Upvotes: 0

lalit choudhary
lalit choudhary

Reputation: 2405

You can use mysql DISTINCT function with your condition.

eg. select distinct(column_name) from table_name WHERE cus_id <> 'C108' AND bus_id <> 'B109';

Upvotes: 0

zibidyum
zibidyum

Reputation: 174

you can use group table seperately and detect duplicated values for each column and then filter table using those values.

    select * from table 
    where cus_id not in
    (select cus_id from table 
    group by cus_id
    having count(*) > 1)
    and
    bus_id not in
    (select bus_id from table 
    group by bus_id
    having count(*) > 1)

Upvotes: 1

jorge.alonso
jorge.alonso

Reputation: 289

I haven't tested, but something like this may work

SELECT bus_id FROM table WHERE bus_id NOT IN (SELECT bus_id FROM table WHERE cus_id = C108)

Upvotes: 0

Andrew
Andrew

Reputation: 5340

Try this:

select bus_id from table where bus_id not in (select bus_id from table where cus_id='c108')

Upvotes: 0

Related Questions