Database statement question

Slaanesh

Say I have two columns, Artist and Song, how do I select rows that have duplicates ? E.g. Counting Crows - Mr. Jones appears twice, are there any statements that will select rows that have another row the same..... sorry very bad at explaining heads a bit wrecked, someone might understand me

Slaan.

flyz

SELECT * FROM Table WHERE Artist == "Counting Crows" AND Song == "Mr Jones"

should work

Slaanesh

I had a feeling that would be the reply considering my craply explained post ! What I meant was, I need to find any duplicate songs in the database, not just one.

Slaan.

Quigs Snr

Select song, title
from table
group by song
having count(song) > 1


or something like that.

Slaanesh

don't think thats it either although I get where your coming from, any other suggestions ?

Quigs Snr

Works in SQL Server.

jmcc

What db type (MySql, SQL Server) is being used? If it is MySQL, the task is trickier as it does not have subselects. Therefore you have to use a left join.

Regards...jmcc

flyz

If there's only one entry for each Artist or Song you could set them to be unique when creating the table.



There's a way of preventing duplicate entries by setting the 2 columns to be unique together, i.e that you can't have more than one entry of 'Counting Crows - Mr Jones' but you could have 'Counting Crows - A Murder Of One'

As for how that works I haven't a clue

Quigs Snr

Flyz, what you are talking about is a composite key. Very Easy to implement in all the Microsoft Databases

d4r3n

im not quite sure what your asking but i think ill have a guess, you have duplicates and you dont want duplicates to be shown?

SELECT DISTINCT artist, song FROM table;

what will select artist and song from table but only show each artist, song once

DeVore

what slaan is trying to explain is this.

suppose he had a table like this

---------------|---------------
   song        |    artist      |
---------------|---------------
     A            |     B
     A            |     B
     C            |     D
     E            |     F
     E            |     F

what he wants back in his record set is

---------------|---------------
   song        |    artist      |
---------------|---------------
     A            |     B
     A            |     B
     E            |     F
     E            |     F

Now in a FULLY relational database (which MycrapSQL isnt).
You'd do as follows:

Select Songs1.song, Songs1.artist

from <tablename> as Songs1 , <tablename> as Songs2

where
songs1.song = songs2.song
and
songs1.artist= songs2.artist

The tricky bit of this is the fact that you treat the same table as if it were two separate tables (you can do this by naming the table in the FROM clause twice.... as I have above.)

No you just think of them as two separate tables and so you are just selecting common rows from two different tables.

Just call me Yoda...

DeV.


ps: all of this is from memory after a few beers so the syntax may be a little wack but it looks good through my beer goggles...

bonkey

Alternately, you should be able to do something like :

SELECT song, artist, count(*)
  FROM mytable
  GROUP BY song, artist
  HAVING count(*) > 1

Which avoids having to do a nasty self-join.
Of course...."Yoda deVore's" self-join may be more efficient, especially if you have your indexes done right.

jc