SQL Server distinct query with two columns in same table

Question

My tables are:

tblProduct

male| female| Brand_ID

and

tblBrands

Brand_ID| Brand_Name

My query is here:

Select distinct   
    a.Male, a.Female, b.Brand_Name 
from 
    tblProduct as a 
inner join
    tblBrands as b on b.Brand_ID = a.Brand_ID

This query returns this output:

Male    Female  Brand_Name
----------------------------------
 1       0        ABC
 0       1        ABC
 1       0        ABC
 1       0        PQR
 1       0        PQR
 0       1        PQR
 0       1        XYZ
 0       1        XYZ

But I want this output shown below - what should I do for to get this?

Male    Female  Brand_Name
 ----------------------------------
 1       1        ABC
 1       1        PQR
 0       1        XYZ

There check is if the brand XYZ is for male or female if is a male bit is true and female bit is true is show XYZ 1 1 or if female or male present according to that show XYZ 1 0 or XYZ 0 1

What is the logic behind the expected output row for Brand_Name = 'xyz'? There is no record where Male = 1. — HoneyBadger
– HoneyBadger, Commented Sep 26, 2017 at 9:48
Please answxer honeyBadger's comment. How did you got to 1 0 XYZ ? — GuidoG
– GuidoG, Commented Sep 26, 2017 at 9:53
there check is if the brand XYZ is for male or female if is a male bit is true and female bit is true is show XYZ 1 1 or if female or male present according to that show XYZ 1 0 or XYZ 0 1 — Tushar
– Tushar, Commented Sep 26, 2017 at 9:55

DhruvJoshi · Accepted Answer · 2017-09-26 09:55:35Z

2

A simple GROUP BY clause will solve your problem

Select   
  Max(cast(a.Male as int)) as Male  , 
  Max(cast(a.Female as int)) as Female,
  b.Brand_Name 
from tblProduct as a inner join
tblBrands as b on b.Brand_ID = a.Brand_ID
group by b.Brand_Name

edited Sep 26, 2017 at 9:55

answered Sep 26, 2017 at 9:49

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tushar Over a year ago

1 and 0 is bit not number

iamsankalp89 Over a year ago

1 0 XYZ this query doesn't give last tuple

DhruvJoshi Over a year ago

@Tushar please see updated solution. Solves your bit problem. Please answer the question about last record

gwt · Accepted Answer · 2017-09-26 10:16:49Z

1

Since you mentioned that Male and Female columns are of type bit then, this is how you can write your query without needing MAX and CAST functions:

 select (case when ((select count(*) from tblProduct a where a.Male=1 and a.Brand_ID=b.Brand_ID) > 0) then 1 else 0 end) Male,
        (case when ((select count(*) from tblProduct a where a.Female=1 and a.Brand_ID=b.Brand_ID) > 0) then 1 else 0 end) Female
        ,b.Brand_Name
 from tblProduct as a inner join
 tblBrand as b on b.Brand_ID = a.Brand_ID
 group by b.Brand_Name,b.Brand_ID

edited Sep 26, 2017 at 10:16

answered Sep 26, 2017 at 10:08

gwt

2,4334 gold badges40 silver badges61 bronze badges

Collectives™ on Stack Overflow

SQL Server distinct query with two columns in same table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related