distinct with multiple field

[siva07]

my $cStr="select distinct artist,company from albuminfo ";


i am using this query to select unique artist and company. But the duplicates also get display.

Is any idea.

Thanks

[dafodil]

select distinct artist, company from albuminfo
your mysql code up there means that there will be distinct artist and company not distinct artist and distinct company.
For example:
Artist--------Company
----------------------------------------
Artist A------Company B
Artist A---- - Company C
Artist A-------Company D
Artist B-------Company A
Artist C-------Company A

Try this first:
select distinct artist from albuminfo
so that you can check whether distinct really works.
If it works, the problem might be in the way you put the command

[siva07]

Thanks for your reply,

I have tried to select only the distinct artist,its working.but if i select both artist and company it gives duplicate records.

is any idea.

[mwasif]

Did you really understand what dafodil told?

What you wanted to display if you have the following data in the table?

Artist--------Company
----------------------------------------
Artist A------Company B
Artist A---- - Company C
Artist A-------Company D
Artist B-------Company A
Artist C-------Company A

Do you want to select Artist A only once regardless of the company? If this is you are looking for then use GROUP BY instead
[CODE=mysql]SELECT artist,company FROM albuminfo GROUP BY artist[/CODE]

[dafodil]

Try this:
[CODE=mysql]SELECT distinct artist, company FROM albuminfo group by artist[/CODE]


If it doesen't work give me a sample of what would you want to display just like my sample on my previous posts.

Sorry I'm still editting my post when you posted I can't delete this anymore.
try it.

Moderator: Kindly use code tags when posting source code.

[rajiv07]

Thank u,
Let me Explain with Sample,

My table name Is ALBUMINFO

Assume It has two fields called ARTIST and COMPANY

ARTIST --------------------------------------> COMPANY
A1 ------------------------------------------> C1
A1 -------------------------------------------> C1
A2--------------------------------------------> C2
A2 ------------------------------------------> C2
A2 ------------------------------------------> C3
A2 ------------------------------------------> C3
Ok,Now i want output ike this

ARTIST---> A1 and A2
COMPANY-->C1 and C2 and C3

[mwasif]

How will you know that which Artist belongs to which company?

[dafodil]

Yeah that's right it should be by pair.. In the first place, you should have created a table with a primary key as artist and a unique key as company, if you want to totally sort them out.

[mwasif]

You can also use GROUP_CONCAT().

[rajiv07]

Table ALBUMINFO

ID---ARTIST --------------------------------------> COMPANY
1----A1 ------------------------------------------> C1
2----A1 -------------------------------------------> C1
3----A2--------------------------------------------> C2
4----A2 ------------------------------------------> C2
5----A2 ------------------------------------------> C3
6----A2 ------------------------------------------> C3



here i am using ID as a primary Key.


my $cStr="select distinct artist from albuminfo ";

my $cStr1="select distinct company from albuminfo ";

Suppose, i use these Query in two steps

The OutPut Will be

ARTIST

A1,A2

and

COMPANY

C1,C2,C3

If i am right,Then i want impelment these two Query into one compained query.
for the Same above Output.

Is Any Idea.

[mwasif]

Try both the queries and see what you require. You can join two queries with UNION.

[CODE=mysql]SELECT DISTINCT artist FROM albuminfo
UNION ALL
SELECT DISTINCT company FROM albuminfo[/CODE]
OR
[CODE=mysql]SELECT GROUP_CONCAT(DI STINCT artist) FROM albuminfo
UNION ALL
SELECT GROUP_CONCAT(DI STINCT company) FROM albuminfo[/CODE]

BTW: Did you try GROUP_CONCAT() I mentioned above?

[Murali Dharan]

Try both the queries and see what you require. You can join two queries with UNION.

[CODE=mysql]SELECT DISTINCT artist FROM albuminfo
UNION ALL
SELECT DISTINCT company FROM albuminfo[/CODE]
OR
[CODE=mysql]SELECT GROUP_CONCAT(DI STINCT artist) FROM albuminfo
UNION ALL
SELECT GROUP_CONCAT(DI STINCT company) FROM albuminfo[/CODE]

BTW: Did you try GROUP_CONCAT() I mentioned above?

U can get more info regarding GROUP_CONCAT() Here.

http://www.discussweb.com/database-support/1401-group_concat-mysql.html