Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: What's the best solution?

    Hi!

    I want to get all the albuns released by an Interpreter, the albuns years, the link for the front cover of the cd and links for external sites where the album is disposable to buy (with the name and logo of the store). With the first outer join everything is fine and (without lines 4 and 5) I get the name of the albuns, their years and the link for the front cover, if exists. If doesn't exist I get just the albName and albYear, as I want.

    The problem begins when I have two stores where it's possible to buy the same album. Doing this complete query I will get two rows for the same album with the different stores and i needed a list without repeated albuns.

    What is the best sollution?

    Get first the table with name, album and artlink and then go get the stores for each album that I have? This has to be done in PHP, right?

    Thanks for your attention!

    Code:
    1. SELECT DISTINCT releasedBy.albName, releasedBy.albYear, artlink, storeName, albLink, logo 
    2. FROM releasedBy LEFT OUTER JOIN Artwork 
    3. ON Artwork.albName = releasedBy.albName AND Artwork.albYear = releasedBy.albYear 
    4. LEFT OUTER JOIN (soldBy NATURAL JOIN Store) 
    5. ON soldBy.albName = releasedBy.albName AND soldBy.albYear = releasedBy.albYear
    6. WHERE intname='Radiohead' AND (artType = 'front' OR artLink IS NULL);
    Last edited by rpOliveira; 12-26-03 at 15:43.
    Ah! Não ser eu toda a gente e toda a parte!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I am going to help you, but what is a pain in my duck-ass is that I have no idea what columns belong to what tables since you don't alias all the columns.

    It's just frustrating is all.

    In the meantime, how about a description of the three tables involved for my reference and a sample output of your query.

    How many stores do you want to show and in what order?
    If you only want to show one store, how do we determine which store YOU want to see?
    Last edited by The_Duck; 12-26-03 at 15:07.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    50

    Sorry.

    Oh, sorry, I thought it was enough! Here are the tables:

    releasedBy(musName, musYear, albName, albYear, intName, nTrack, nCd])

    Artwork(artLink, artType, albName, albYear)

    soldBy(albName, albYear, storeName, albLink)

    Store(storeName, Logo)

    When I made just the first outer join I obtained the table:

    ALBNAME | ALBYEAR | ARTLINK | STORENAME | ALBLINK | LOGO

    where, correctly, there's no repeated albnames.

    Executing all the query presented there are repeated albnames whenever the album it's buyable in more than one store - we have more than one storename, albLink and logo for an albname.

    As I don't want repeated albnames I thougt to do the first outer join only and, for each album, get the stores independently, building this way the table in html using PHP. What do you think?
    Last edited by rpOliveira; 12-26-03 at 15:30.
    Ah! Não ser eu toda a gente e toda a parte!

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    once you add stores you end up getting multiple entries since this adds to your distinct set (the stores now become distinct in themselves with each wonderful Radiohead album)

    Like you stated, if you don't want duplicate entries for the albums, then you need to have two queries OR you can make the store-names columns in each album row. This can be done with a DECODE.

    Do you know ALL the possible stores?
    Like -
    Possible stores only equal: TOWER, HMV, NEWBURY COMICS, STRAWBERRIES

    or are there too many stores?

    I was thinking like this:
    Code:
    ALBNAME | ALBYEAR | ARTLINK | ALBLINK | LOGO | TOWER RECORDS | HMV | NEWBURY | SAM GOODY
    The Bends | 1997 | artlink.com | alblink.com | logo.com | towerlink.com | (null) | newburylink.com | (null)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2003
    Posts
    50

    Thanks

    Thanks. I did not know the Decode Function. I will try it.
    Ah! Não ser eu toda a gente e toda a parte!

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    If you know all possible stores then you can do it.

    just keep in mind you have the stores and then you need two other columns to pivot on, then get the rest of your data.

    http://asktom.oracle.com
    this link has great info on pivoting rows / columns
    just do a search on Pivot.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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