CODEWITHSUNDEEP
sql
Amit J
Amit J

Reputation: 9

finding unique rows in SQL

I have the following two tables:

Table 1 :

Row_Id    SR_NUM
------------------
100       1-12345
101       1-34567

Table 2:

SRA_Id   Value
----------------
100      Test
101      (Blank)
100      (Blank)
100      (Blank)
101      (Blank)

When I am creating the following query:

SELECT DISTINCT 
    a. SR_NUM,
    b.value
FROM
    Table_1 a,
    Table_2 b
WHERE 
    a.row_id = b.sra_id

I am getting 3 records in output (2 for 100 - one with value as 'Test' and another value as (Blank))

I want it to show only 1 record for 100 with value and not the blank one.

Can you please let me know what I am doing wrong?

Thanks.

Upvotes: 0

Views: 90

Answers (2)

HFR1994
HFR1994

Reputation: 566

I don't know if this is what you want but you can try:

SELECT a.SR_NUM, b.value FROM Table_1 as a 
JOIN Table_2 as b ON (a.row_id = b.sra_id AND b.value != "(BLANK)");

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

GROUP BY a.SR_NUM and choose e.g. the MAX(b.VALUE) might be what you want.

SELECT a.SR_NUM,
       MAX(b.VALUE) VALUE
       FROM Table_1 a
            INNER JOIN Table_2 b
                       ON a.ROW_ID = b.SRA_ID
       GROUP BY a.SR_NUM;

(If MAX() gives you the "(Blank)" ones (I assume you mean NULL) replace it with MIN().)

Upvotes: 1

Related Questions