distinct with multiple field

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

re: distinct with multiple field

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

re: distinct with multiple field

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.

re: distinct with multiple field

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

re: distinct with multiple field

Try this:

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.

re: distinct with multiple field

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

re: distinct with multiple field

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

re: distinct with multiple field

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.

re: distinct with multiple field

You can also use GROUP_CONCAT().

re: distinct with multiple field

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.

re: distinct with multiple field

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

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

re: distinct with multiple field

U can get more info regarding GROUP_CONCAT() Here.
http://www.discussweb.com/database-s...cat-mysql.html