how to remove repeated records

Question

0.00/5 (No votes)

See more:

i have the follwing two tables;

music containing the fowwing fields:

SQL

music_id  movie_name Movie_image
  1         XXX        1.jpg
  2        YYY        2.jpg

music_details is the second table which has foreign key reference of music_id
and contains the following fields.

SQL

music_id   songtitle   Lyricist   Artist   
1           song1       aaa        bbbb
1           song2       bbb        ccc
1           song3       ccc        ddd

now i need to get the details from data base like:

SQL

music_id  music_name   music_image   Songtitle  Lyricist    Artist
1          XXX          1.jpg          song1      aaa        bbbb
                                       song2      bbb        ccc
                                       song3      ccc        ddd

i used joins but it displaying movie_names and images foreach song.
but i need movie_name and movie_image only once for all songs.. Is there any way to display like this.

Posted 7-Jul-13 20:14pm

User-10031173

Updated 7-Jul-13 20:23pm

v2

Add a Solution

Comments

bbirajdar 8-Jul-13 2:34am

show your code

Maciej Los 8-Jul-13 5:15am

Yes, it is, using grouping in reports (for example: CrystalReports)!
Do not do it in clear T-SQL!

jaideepsinh 8-Jul-13 6:06am

Hi swapna,
You want music_name and music_image column only at once and then it saw blank or null?

User-10031173 8-Jul-13 6:27am

it should be blank.(mysql)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-07T23:29:00

First of all, read my comment to the question.

Secondly...
If you really MUST to do it in T-SQL, you need to add number for each row (MySQL ROW_NUMBER()[^]) and then to use CASE ... END[^] statement, for example:

SQL

SET @row = 0; 

SELECT RowNo, CASE WHEN RowNo =1 THEN music_id ELSE NULL END CASE AS music_id, CASE WHEN RowNo =1 THEN music_name ELSE NULL END CASE AS music_name,   CASE WHEN RowNo =1 THEN music_image ELSE NULL END CASE AS music_image, Songtitle, Lyricist, Artist
FROM (
    SELECT @row = @row +1 AS RowNo, t1.music_id, t1.movie_name AS music_name, t1.Movie_image AS music_image, t2.songtitle, t2.Lyricist, t2.Artist  
    FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.music_id = t2.music_id
) AS T

Rajan Maheshwari · Answer 2 · 2013-07-07T20:44:00

Solution 1

use distinct keyword in ur sql query
ex select distinct movie_name from table;

It will show your Movie name only once

else if you are using database obviously records with foreign key repeats.
The one you are talking is practically impossible as it will violate the first constraint rule of database.

Posted 7-Jul-13 20:44pm

Rajan Maheshwari

Updated 7-Jul-13 20:46pm

v2

**Nirav Prabtani** · Answer 3 · 2013-07-07T20:50:00

Solution 2

Use Distinct in your Query

SQL

SELECT distinct ColumnName From TableName

Posted 7-Jul-13 20:50pm

Nirav Prabtani

Comments

User-10031173 8-Jul-13 2:55am

i have already use distinct keyword but is not solve my problem.

Nirav Prabtani 8-Jul-13 2:58am

Can you show your query??

User-10031173 8-Jul-13 3:29am

select music.movie_name,music.movie_image,music_details.songtitle from music join music_details on music.musci_id=music_details.music_id group by music_id .

Nirav Prabtani 8-Jul-13 3:32am

what column would you like to select distinct???

User-10031173 8-Jul-13 4:07am

movie_image and Movie_name