Professional Web Applications Themes

Delete against multiple tables + And / Or - MySQL

Hi there, a part of my music site exists of three tables: songs -> id, artist_id, song_name lyrics -> id, song_id, lyrics genre -> id, song_id, genre_name Now to delete the song (1 record), *possbile* lyrics (maybe 1 record, maybe none), and *possible* genre-links (max unlimited records), i use the following query: DELETE s.*, l.*, c.* FROM `songs` s, `lyrics` l, `genres` g WHERE s.`id` = $id AND l.`song_id` = $id AND g.`song_id` = $id Which works _IF_ the song also has lyrics AND genres. Now of course, a song also has to be able to be deleted if it ...

  1. #1

    Default Delete against multiple tables + And / Or

    Hi there,

    a part of my music site exists of three tables:
    songs -> id, artist_id, song_name
    lyrics -> id, song_id, lyrics
    genre -> id, song_id, genre_name

    Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
    maybe none), and *possible* genre-links (max unlimited records),
    i use the following query:

    DELETE s.*, l.*, c.*
    FROM `songs` s,
    `lyrics` l,
    `genres` g
    WHERE s.`id` = $id
    AND l.`song_id` = $id
    AND g.`song_id` = $id

    Which works _IF_ the song also has lyrics AND genres.
    Now of course, a song also has to be able to be deleted if it
    hasn't got lyrics, or genres.

    I tried replacing AND in the query with OR, but that kind of emptied my
    songs-table ... :(

    Any clues? Any help would be great.

    Frizzle.

    frizzle Guest

  2. #2

    Default Re: Delete against multiple tables + And / Or

    "frizzle" <phpfrizzle> wrote:
    > songs -> id, artist_id, song_name
    > lyrics -> id, song_id, lyrics
    > genre -> id, song_id, genre_name
    >
    > Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
    > maybe none), and *possible* genre-links (max unlimited records),
    > i use the following query:
    >
    > DELETE s.*, l.*, c.*
    > FROM `songs` s,
    > `lyrics` l,
    > `genres` g
    > WHERE s.`id` = $id
    > AND l.`song_id` = $id
    > AND g.`song_id` = $id
    Why not using 3 DELETE statements? Alternatively (much better) you may
    go for real foreign key constraints and the ON DELETE CASCADE option.

    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  3. #3

    Default Re: Delete against multiple tables + And / Or


    Axel Schwenke wrote:
    > "frizzle" <phpfrizzle> wrote:
    >
    > > songs -> id, artist_id, song_name
    > > lyrics -> id, song_id, lyrics
    > > genre -> id, song_id, genre_name
    > >
    > > Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
    > > maybe none), and *possible* genre-links (max unlimited records),
    > > i use the following query:
    > >
    > > DELETE s.*, l.*, c.*
    > > FROM `songs` s,
    > > `lyrics` l,
    > > `genres` g
    > > WHERE s.`id` = $id
    > > AND l.`song_id` = $id
    > > AND g.`song_id` = $id
    >
    > Why not using 3 DELETE statements? Alternatively (much better) you may
    > go for real foreign key constraints and the ON DELETE CASCADE option.
    >
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    >
    >
    > XL
    > --
    > Axel Schwenke, Senior Software Developer, MySQL AB
    >
    > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    I assumed a single query would be faster.
    I will look in your options later on. Thanks a lot.

    Frizzle.

    frizzle Guest

  4. #4

    Default Re: Delete against multiple tables + And / Or


    Axel Schwenke wrote:
    > "frizzle" <phpfrizzle> wrote:
    >
    > > songs -> id, artist_id, song_name
    > > lyrics -> id, song_id, lyrics
    > > genre -> id, song_id, genre_name
    > >
    > > Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
    > > maybe none), and *possible* genre-links (max unlimited records),
    > > i use the following query:
    > >
    > > DELETE s.*, l.*, c.*
    > > FROM `songs` s,
    > > `lyrics` l,
    > > `genres` g
    > > WHERE s.`id` = $id
    > > AND l.`song_id` = $id
    > > AND g.`song_id` = $id
    >
    > Why not using 3 DELETE statements? Alternatively (much better) you may
    > go for real foreign key constraints and the ON DELETE CASCADE option.
    >
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    >
    >
    > XL
    > --
    > Axel Schwenke, Senior Software Developer, MySQL AB
    >
    > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    I haven't used foreign keys, because i have no experience with them
    (yet).
    Might be a good time to start with it ...

    Frizzle.

    frizzle Guest

  5. #5

    Default Re: Delete against multiple tables + And / Or


    frizzle wrote:
    > Axel Schwenke wrote:
    > > "frizzle" <phpfrizzle> wrote:
    > >
    > > > songs -> id, artist_id, song_name
    > > > lyrics -> id, song_id, lyrics
    > > > genre -> id, song_id, genre_name
    > > >
    > > > Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
    > > > maybe none), and *possible* genre-links (max unlimited records),
    > > > i use the following query:
    > > >
    > > > DELETE s.*, l.*, c.*
    > > > FROM `songs` s,
    > > > `lyrics` l,
    > > > `genres` g
    > > > WHERE s.`id` = $id
    > > > AND l.`song_id` = $id
    > > > AND g.`song_id` = $id
    > >
    > > Why not using 3 DELETE statements? Alternatively (much better) you may
    > > go for real foreign key constraints and the ON DELETE CASCADE option.
    > >
    > > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    > >
    > >
    > > XL
    > > --
    > > Axel Schwenke, Senior Software Developer, MySQL AB
    > >
    > > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    >
    > I haven't used foreign keys, because i have no experience with them
    > (yet).
    > Might be a good time to start with it ...
    >
    > Frizzle.
    Also, consider revising your structure.

    A song (or at least any given version of a song) can only have one set
    of lyrics. Similarly, any given set of lyrics is likely to belong to
    just one song - so these might as well be stored in the same table.

    Genres exist independently of specific songs - and songs can belong to
    more than one genre so there should be a separate table that relates
    songs to genres - comprosing of a primary key made up from the song_id
    and the genre_id.

    Thinking about it you could go further. Songs are just words and music
    written down. Each song has a composer (or composers) who may or may
    not be the performing artist.
    So you might instead have a table of 'tracks' where a track consists of
    a track_id,song_id,(performing)artist_id, and length - and it would
    then be this track_id that you'd associate with a genre.

    So the structure might then look like this...

    songs
    song_id | song_name | (composing)artist_id
    1 | 'Rocket Man' | 1
    2 | 'Yesterday' | 2
    3 | 'Something' | 3
    4 | 'What'd I Say'| 4

    genres
    genre_id | genre |
    1 | pop |
    2 | rock |
    3 | blues |

    artists
    artist_id | artist
    1 | Bernie Taupin & Elton John
    2 | John Lennon & Paul McCartney
    3 | George Harrison
    4 | Ray Charles
    5 | The Beatles
    6 | Elton John
    tracks
    track_id | song_id | (performing)artist_id | length | released
    1 | 1 | 6 | 3:54 | 1972
    2 | 2 | 5 | 3:03 | 1965
    3 | 3 | 5 | 4:01 | 1969
    4 | 4 | 4 | 3:34 | 1959

    trackID_genreID
    track_id | genre_id
    1 | 1
    1 | 2
    2 | 1
    2 | 2
    3 | 1
    3 | 2
    4 | 3

    albums
    album_id | album_name | released
    etc | etc

    albumID| track_no| track_id |
    and so on

    hell, i'm going to write my own music db

    strawberry Guest

  6. #6

    Default Re: Delete against multiple tables + And / Or


    strawberry wrote:
    > frizzle wrote:
    > > Axel Schwenke wrote:
    > > > "frizzle" <phpfrizzle> wrote:
    > > >
    > > > > songs -> id, artist_id, song_name
    > > > > lyrics -> id, song_id, lyrics
    > > > > genre -> id, song_id, genre_name
    > > > >
    > > > > Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
    > > > > maybe none), and *possible* genre-links (max unlimited records),
    > > > > i use the following query:
    > > > >
    > > > > DELETE s.*, l.*, c.*
    > > > > FROM `songs` s,
    > > > > `lyrics` l,
    > > > > `genres` g
    > > > > WHERE s.`id` = $id
    > > > > AND l.`song_id` = $id
    > > > > AND g.`song_id` = $id
    > > >
    > > > Why not using 3 DELETE statements? Alternatively (much better) you may
    > > > go for real foreign key constraints and the ON DELETE CASCADE option.
    > > >
    > > > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    > > >
    > > >
    > > > XL
    > > > --
    > > > Axel Schwenke, Senior Software Developer, MySQL AB
    > > >
    > > > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > > > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    > >
    > > I haven't used foreign keys, because i have no experience with them
    > > (yet).
    > > Might be a good time to start with it ...
    > >
    > > Frizzle.
    >
    > Also, consider revising your structure.
    >
    > A song (or at least any given version of a song) can only have one set
    > of lyrics. Similarly, any given set of lyrics is likely to belong to
    > just one song - so these might as well be stored in the same table.
    >
    > Genres exist independently of specific songs - and songs can belong to
    > more than one genre so there should be a separate table that relates
    > songs to genres - comprosing of a primary key made up from the song_id
    > and the genre_id.
    >
    > Thinking about it you could go further. Songs are just words and music
    > written down. Each song has a composer (or composers) who may or may
    > not be the performing artist.
    > So you might instead have a table of 'tracks' where a track consists of
    > a track_id,song_id,(performing)artist_id, and length - and it would
    > then be this track_id that you'd associate with a genre.
    >
    > So the structure might then look like this...
    >
    > songs
    > song_id | song_name | (composing)artist_id
    > 1 | 'Rocket Man' | 1
    > 2 | 'Yesterday' | 2
    > 3 | 'Something' | 3
    > 4 | 'What'd I Say'| 4
    >
    > genres
    > genre_id | genre |
    > 1 | pop |
    > 2 | rock |
    > 3 | blues |
    >
    > artists
    > artist_id | artist
    > 1 | Bernie Taupin & Elton John
    > 2 | John Lennon & Paul McCartney
    > 3 | George Harrison
    > 4 | Ray Charles
    > 5 | The Beatles
    > 6 | Elton John
    > tracks
    > track_id | song_id | (performing)artist_id | length | released
    > 1 | 1 | 6 | 3:54 | 1972
    > 2 | 2 | 5 | 3:03 | 1965
    > 3 | 3 | 5 | 4:01 | 1969
    > 4 | 4 | 4 | 3:34 | 1959
    >
    > trackID_genreID
    > track_id | genre_id
    > 1 | 1
    > 1 | 2
    > 2 | 1
    > 2 | 2
    > 3 | 1
    > 3 | 2
    > 4 | 3
    >
    > albums
    > album_id | album_name | released
    > etc | etc
    >
    > albumID| track_no| track_id |
    > and so on
    >
    > hell, i'm going to write my own music db
    First of all, i decided not to put the lyrics in the same table,
    because a while ago i learned that mysql reads all data, and then dumps
    what's not in the SELECT statement.
    I figured including the lyrics in the songs table would be quite large
    in comparison of the rest of info; some title, artist_id and duration
    ....

    Please do correct me if what i thought about the selecting & dumping is
    wrong ...

    Secondly, what you suggest is sort of what i have. I will roughly
    sketch it below.
    The ideas you have, i also had. I want to build an extensive music DB
    in the near future.
    This one right now is just for some simple track listing. I want to
    keep it as clean as possible, so the effort of adding a song doesn't
    need to be preceeded by adding albums, track no's etc.
    ( Did you already think of a way to handle albums made by various
    artists, or songs with the cooperation of another artist, or even
    multiple, or bands constructed for the occasion ? (like Band Aid)
    People swapping bands, previous band members, People swapping
    recordlabels, etc. etc. etc. The more you think about it, the more
    comes to show, what a nice challenge the future holds for me ... :) )

    Rough table sketch current structure:

    - Artists -> Id, Name, some additional info
    - Songs -> Id, Artist_id, Name, duration, etc.
    - Genres -> Id, Name
    - Moods -> Id, Name
    - Combo_artist_genre -> Id, Artist_id, Genre_id
    - Combo_songs_moods -> Id, Song_id, Mood_id


    Frizzle.

    frizzle Guest

Similar Threads

  1. update multiple records in multiple tables from one form
    By Anj01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 17th, 05:54 AM
  2. Delete multiple rows
    By Darren Carter in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 12th, 10:33 PM
  3. How do you delete a record form multiple tables
    By Mike in forum ASP Database
    Replies: 7
    Last Post: December 18th, 05:50 PM
  4. multiple delete with checkboxes ?
    By Fredrik/Sweden in forum ASP Database
    Replies: 3
    Last Post: October 2nd, 03:53 PM
  5. can you delete multiple frames at once?
    By gooskimo webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 1
    Last Post: July 24th, 03:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139