Repeating in sql query, how to remove

[Ntshoekeleng]

SELECT ARTWORK.TITLE AS 'Title',ARTWORK.DATE_LISTED AS 'Date Listed',
    ARTWORK.USUAL_TYPE as 'Type',ARTWORK.USUAL_MEDIUM as 'Medium', ARTWORK.USUAL_STYLE as 'Style',
    ARTWORK.YEAR_COMPLETED as 'Year',ARTWORK_SALES.ASKING_PRICE as 'Asking Price'
 
    FROM ARTWORK_SALES,ARTWORK_SOLD
 
    INNER JOIN ARTWORK ON ARTWORK.ARTWORK_CODE=ARTWORK.ARTWORK_CODE
    GROUP BY TITLE, ARTWORK.TITLE,ARTWORK.DATE_LISTED,ARTWORK.USUAL_TYPE,ARTWORK.USUAL_MEDIUM,ARTWORK.USUAL_STYLE,
    ARTWORK.YEAR_COMPLETED,ARTWORK_SALES.ASKING_PRICE 

That is my code and i get the correct result but they repeat each row

[India777]

Please Post your sample result for this Query

[Ntshoekeleng]

Love lost 2011-07-12 00:00:00.000 Painting Oil Contempory 2011-05-15 00:00:00.000 15000.00
Love lost 2011-07-12 00:00:00.000 Painting Oil Contempory 2011-05-15 00:00:00.000 150000.00
My Queen 2011-10-10 00:00:00.000 Sculpture Mixed Folk 2009-08-17 00:00:00.000 15000.00
My Queen 2011-10-10 00:00:00.000 Sculpture Mixed Folk 2009-08-17 00:00:00.000 150000.00
Reality 2011-08-17 00:00:00.000 Sculpture Marble Impressionist 2010-05-28 00:00:00.000 15000.00
Reality 2011-08-17 00:00:00.000 Sculpture Marble Impressionist 2010-05-28 00:00:00.000 150000.00

[India777]

use distinct keyword after select keyword in Sql Query

Example: Select distinct form tablename

[India777]

oh Sorry i cant see Correctly.
What you Actually want to Display in "Asking Price" Field

[Ntshoekeleng]

still doesn't work..

[India777]

The problem is with the field name "Asking Price". Because it Contain 15000.00 in first row then 150000.00 in Second Row. So group by cannot Work here.

So What You want to Display in this Field Actually?

[Ntshoekeleng]

Oh ok..Il see how i solve this one.

[India777]

What is the Common Field for ARTWORK_SALES and ARTWORK Tables.

[Ntshoekeleng]

The common field is artwork_code

[India777]

but why you check like this in inner join condition
ARTWORK.ARTWORK_CODE=ARTWORK.ARTWORK_CODE
Change this like
ARTWORK.ARTWORK_CODE=ARTWORK_SALES.ARTWORK_CODE.

You need to Sum the "Asking Price" in ARTWORK_SALES by ARTWORK_CODE Right?

[Ntshoekeleng]

Inner join artwork on artwork.artwork_code=artwork_sold.artwork_code

i have used this code and now it repeats twice and not for times

[India777]

ok Ntshoekeleng. Please Say What You need to Display in "Asking Price" field?
15000.00 or 150000.00 or Sum of this two Values(From Your Result).

[Ntshoekeleng]

The sum of the two values

[India777]

So You need to Change the Query. Please Remove "Asking Price" from Group by and put Sum(ARTWORK_SALES.ASKING_PRICE) in Select Query. Please find this Link http://w3schools.com/sql/sql_func_sum.asp . Its help You To know about Sum() function in Sql.

[sonurathore]

SELECT distinct ARTWORK.TITLE AS 'Title',ARTWORK.DATE_LISTED AS 'Date Listed',
ARTWORK.USUAL_TYPE as 'Type',ARTWORK.USUAL_MEDIUM as 'Medium', ARTWORK.USUAL_STYLE as 'Style',
ARTWORK.YEAR_COMPLETED as 'Year',ARTWORK_SALES.ASKING_PRICE as 'Asking Price'
 
FROM ARTWORK_SALES,ARTWORK_SOLD
 
INNER JOIN ARTWORK ON ARTWORK.ARTWORK_CODE=ARTWORK.ARTWORK_CODE
GROUP BY TITLE, ARTWORK.TITLE,ARTWORK.DATE_LISTED,ARTWORK.USUAL_TY PE,ARTWORK.USUAL_MEDIUM,ARTWORK.USUAL_STYLE,
ARTWORK.YEAR_COMPLETED,ARTWORK_SALES.ASKING_PRICE 

try this after ask me

[India777]

sonurathore, please see the Result of the Query

Love lost 2011-07-12 00:00:00.000 Painting Oil Contempory 2011-05-15 00:00:00.000 15000.00
Love lost 2011-07-12 00:00:00.000 Painting Oil Contempory 2011-05-15 00:00:00.000 150000.00
 

Distinct is not Work here.

[Rabbit]

Aren't you trying to SUM up the field? I don't see SUM anywhere in your code.

Also, please use code tags when posting code.