SitePoint Sponsor

User Tag List

Results 1 to 24 of 24

Hybrid View

  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)

    Join Mysql tables; 1 contains the names, the other contains multiple links to names

    I am at a loss here and am not sure what to search for!

    I have a table "name" with performer_ID and performer_name.

    I have another table ( association ) with a lot of data including 12 name cells which contain the performer_ID number from the "name" table.


    The reason for this is that the performer can be in any one or more than one of the 12 cells.
    I have a similar problem to the one above for location but there are only two links in the association table this time.

    I also have a similar situation for source and here I use a simple join. This works well as the source is only linked in one place.


    When displaying the data I need to select the name from the performer table for each of the performers in the association table.

    How would I get around the performer problem?

    Should I have designed the database in a different way, possibly with a intermediate table?

    These are the three tables I mentioned above and the current join I am using.
    CREATE TABLE IF NOT EXISTS `performer` (
    `per_ID` smallint(4) NOT NULL AUTO_INCREMENT,
    `full_name` varchar(50) NOT NULL DEFAULT 'Name',
    PRIMARY KEY (`per_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=19 ;


    CREATE TABLE IF NOT EXISTS `source` (
    `sou_ID` smallint(4) NOT NULL AUTO_INCREMENT,
    `origin` varchar(50) NOT NULL DEFAULT 'Name',
    PRIMARY KEY (`sou_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Where the record came from' AUTO_INCREMENT=2 ;

    CREATE TABLE IF NOT EXISTS `location` (
    `loc_ID` smallint(4) NOT NULL AUTO_INCREMENT,
    `place` varchar(50) NOT NULL DEFAULT 'Place',
    PRIMARY KEY (`loc_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=5 ;


    CREATE TABLE IF NOT EXISTS `association` (
    `ass_ID` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
    `performer_1` int(4) NOT NULL COMMENT 'Artist A1',
    `performer_2` int(4) DEFAULT NULL COMMENT 'Artist A2',
    `performer_3` int(4) DEFAULT NULL COMMENT 'Artist A3',
    `performer_4` int(4) DEFAULT NULL COMMENT 'Artist A4',
    `performer_5` int(4) DEFAULT NULL COMMENT 'Artist A5',
    `performer_6` int(4) DEFAULT NULL COMMENT 'Artist A6',
    `performer_7` int(4) NOT NULL COMMENT 'Artist B1',
    `performer_8` int(4) DEFAULT NULL COMMENT 'Artist B2',
    `performer_9` int(4) DEFAULT NULL COMMENT 'Artist B3',
    `performer_10` int(4) DEFAULT NULL COMMENT 'Artist B4',
    `performer_11` int(4) DEFAULT NULL COMMENT 'Artist B5',
    `location_A` int(4) NOT NULL COMMENT 'Where side A recorded',
    `location_B` int(4) NOT NULL COMMENT 'Where side B recorded',
    `source` int(4) NOT NULL COMMENT 'Source of the record',
    PRIMARY KEY (`ass_ID`),
    UNIQUE KEY `number` (`number`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


    Code:
    SELECT * FROM association
    JOIN
    performer
    
    ON
    performer.per_ID=association.performer_1
    
    JOIN
    source
    
    ON
    source.sou_ID=association.source
    
    JOIN 
    location
    
    ON 
        location.loc_ID=association.location_A
    
    WHERE
    number = $number ");

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rubble View Post
    I have another table ( association ) with a lot of data including 12 name cells which contain the performer_ID number from the "name" table.
    i am just going out the door so no time to go into specifics

    google "first normal form"

    redesign your tables and try again

    i hope to be back online tomorrow
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I am afraid I do not understanding what I am finding on the web and need a pointer.

    I understand I now need a table containing a performer_ID and a name_ID between the names table and the association table.

    The names table will stay as it is but the association table is the confusing part. I will still need the 12 performer columns but will the performer value stay the same for every record or will there be a new performer value for each record.

    e.g
    Record 1
    performer_1 = 1
    performer_2 = 2
    "
    "
    Record 2
    performer_1 = 1
    performer_2 = 2
    "
    "
    OR
    Record 1
    performer_1 = 1
    performer_2 = 2
    "
    "
    Record 2
    performer_1 = 11
    performer_2 = 12
    "
    "

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you do a small exercise for me please

    take each pair of your entities and describe two relationships, one in each direction

    for example...
    - each order belongs to only one customer
    - each customer can have multiple orders

    in your case, i have yet to understand what kind of a thing a performer is associated with
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I will have at a guess 2000 performers and 3000 records.

    To save space and insure that the performers name is always spelt the correct way during input I have a table containing the names which I look up to populate the input form.
    The performer value in the main table will contain the ID of the name so when I display the data the main table will look in the names table for the name and display that not the name ID.

    I would describe it as one direction: each performer has one name.

    BUT now thinking about this after digesting your question; to also search which records are linked to each performer it should be:

    Each performer has one name
    Each name has many record numbers.


    I will try and illustrate this a bit better later when I have time.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    I believe the table name "association" doesn't describe the entity, it describes the table's purpose. Which makes it harder to understand the concept of normalization.

    "perfomer" is ok, "location" also.

    Maybe the entity that is missing is "play" ? Just guessing of course.
    Then you could say (Rudy's exercise):
    - A performer can participate in multiple plays
    - A play can have multiple performers

    Etc.

  7. #7
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    Thanks for trying to clear up the confusion Guido2004; hopefuly this diagram should help:
    record_diagram.PNG

    The performer in the main table contains the ID of the performer name in the name table.
    When I display the data I want the performers name displayed and not the ID.

    The location in the main table contains the ID of the location in the location table.


    When you search for the records containing a certain performer it will display all the data in the association table with the ID's replaced by the names and locations.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rubble View Post
    hopefuly this diagram should help:
    so you are associating performers to albums?

    each album can be recorded at multiple locations
    each location can have multiple albums recorded there
    ... so album-location is many-to-many

    each performer can be recorded on many albums
    each album can have many performers
    ... so performer-album is many-to-many

    would you agree with this analysis?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    each album can be recorded at multiple locations
    each location can have multiple albums recorded there
    ... so album-location is many-to-many

    each performer can be recorded on many albums
    each album can have many performers
    ... so performer-album is many-to-many
    Yes I agree with that @r937 ;

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, great, so you need three basic entity tables --

    performer ( id, name, other attributes )
    location ( id, name )
    album ( id, size, title, source )

    with me so far?

    there is more to come
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    Yes I have those already

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rubble View Post
    Yes I have those already
    oh? that's not what you've been posting

    up till now, there was only performer and location, and a very dubious "association" or "main" table that will, frankly, have to go
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    These are the tables I have:

    Code:
    -- Table structure for table `association`
    
    CREATE TABLE IF NOT EXISTS `association` (
      `ass_ID` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
      `number` int(5) NOT NULL COMMENT 'File Number',
      `company` int(4) NOT NULL COMMENT 'Record company',
      `performer_1` int(4) NOT NULL COMMENT 'Artist A1',
      `performer_2` int(4) DEFAULT NULL COMMENT 'Artist A2',
      `performer_3` int(4) DEFAULT NULL COMMENT 'Artist A3',
      `performer_4` int(4) DEFAULT NULL COMMENT 'Artist A4',
      `performer_5` int(4) DEFAULT NULL COMMENT 'Artist A5',
      `performer_6` int(4) DEFAULT NULL COMMENT 'Artist A6',
      `performer_7` int(4) NOT NULL COMMENT 'Artist B1',
      `performer_8` int(4) DEFAULT NULL COMMENT 'Artist B2',
      `performer_9` int(4) DEFAULT NULL COMMENT 'Artist B3',
      `performer_10` int(4) DEFAULT NULL COMMENT 'Artist B4',
      `performer_11` int(4) DEFAULT NULL COMMENT 'Artist B5',
      `performer_12` int(4) DEFAULT NULL COMMENT 'Artist B6',
      `title_A` int(4) NOT NULL COMMENT 'Track name side A',
      `title_B` int(4) NOT NULL COMMENT 'Track name side B',
      `speed_A` int(6) NOT NULL COMMENT 'Speed side A',
      `speed_B` int(6) NOT NULL COMMENT 'Side B speed',
      `location_A` int(4) NOT NULL COMMENT 'Where side A recorded',
      `location_B` int(4) NOT NULL COMMENT 'Where side B recorded',
      `date_A` date NOT NULL COMMENT 'Date side A recorderd',
      `date_B` date NOT NULL COMMENT 'Date side B recorderd',
      `catalogue` varchar(12) NOT NULL COMMENT 'Catalogue number',
      `size` enum('8','10','Other') NOT NULL COMMENT 'Record size',
      `source` int(4) NOT NULL COMMENT 'Source of the record',
      `date_aq` date NOT NULL COMMENT 'Purchase date',
      `purchase` int(6) NOT NULL COMMENT 'Purchase price',
      `est_value` int(6) NOT NULL COMMENT 'Current estimated value',
      `sold` int(6) NOT NULL COMMENT 'Sold price',
      `matrix_A` varchar(12) NOT NULL COMMENT 'Matrix number',
      `matrix_B` varchar(12) NOT NULL COMMENT 'Matrix number',
      `notes` varchar(255) DEFAULT NULL COMMENT 'Any notes',
      PRIMARY KEY (`ass_ID`),
      UNIQUE KEY `number` (`number`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
    
    -- --------------------------------------------------------
    
    
    -- Table structure for table `company`
    
    CREATE TABLE IF NOT EXISTS `company` (
      `comp_ID` smallint(4) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
      `name` varchar(50) NOT NULL DEFAULT 'Enter company' COMMENT 'Record company name',
      PRIMARY KEY (`comp_ID`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Record company names' AUTO_INCREMENT=12 ;
    
    -- --------------------------------------------------------
    
    -- Table structure for table `location`
    
    CREATE TABLE IF NOT EXISTS `location` (
      `loc_ID` smallint(4) NOT NULL AUTO_INCREMENT,
      `place` varchar(50) NOT NULL DEFAULT 'Place',
      PRIMARY KEY (`loc_ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=5 ;
    
    -- --------------------------------------------------------
    
    -- Table structure for table `performer`
    
    CREATE TABLE IF NOT EXISTS `performer` (
      `per_ID` smallint(4) NOT NULL AUTO_INCREMENT,
      `full_name` varchar(50) NOT NULL DEFAULT 'Name',
      PRIMARY KEY (`per_ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=19 ;
    
    --
    There is a titleA & B as well as a source column which I have not decided how to deal with yet. I was waiting to see how the performer part worked and go from there.

    The size and speed were going to be small integers anyway and I did not see any point in having separate tables for those.
    Catalogue number, matrix A and B numbers as well as the dates were all going to be saved into the main table as it was unlikely there were going to be any of those that were the same and I thought it would be simpler.

    The company table worked with a simple join in the test I have tried and I assumed it would carry on that way. Again I know now it is a many-to-many association :
    SELECT * FROM association
    JOIN
    company
    ON
    company.comp_ID=association.company

    WHERE number = $number
    I am sorry for the confusion but I was thinking that if I had the performer part working I could then modify the code for anything else like the location.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i think you're trying to get ahead of me, and was trying to lead you through the steps logically

    rename your association table as the album table, and remove all the performer columns

    regarding locations, are you saying that there will only ever be at most 2 locations, one for each "side" of the album?

    we're talking vinyl here, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    Sorry r937 as that was what I had before we started and I am happy to start over as I want to see how it all comes together.

    We are talking 78's which I am told have one track on either side. It is not my area of expertise and I am trying to help someone else and the information he wants is everything listed in the association table.

    `number` The owners file number as he has the information on paper already - there will only be one
    `company` The record company - there will only be one
    `performer` May just be one per side of the record but could be as many as six
    `title`Track title - one per side
    `speed` RPM - one per side ( there was not a standard speed to begin with!)
    `location` - one per side
    `date recorded` - one per side
    `catalogue` Catalogue number - one per record
    `size` - Record size - one per record ( there was not a standard size to begin with!)
    `source` - Where the record was purchased
    `date_aq` - Date the record was purchased
    `purchase` - Purchase price',
    `est_value` - Current estimated value',
    `sold` - Sold price ( default of 0 if not sold )
    `matrix` - one per side ( alphanumeric codes on the record )
    `notes` - Any notes - one per record

    I have renamed my table to album and removed the performers.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, now we're getting somewhere

    here's the key point -- unless you are going to want to write insanely complex queries, or the number of albums is going to climb into the millions, i cannot imagine that table scans, which normally should be avoided, will adversely affect the performance of your application

    therefore, we can bypass many of the normal rules and conventions for database design in favour of simplifying the structure for ease of maintenance

    "lookup" tables, which typically contain an id and a name, are not required here, and you can simply store the name (e.g. in your original source table) as a VARCHAR column, so 2 location colulmns in the album table

    you do, though, need a many-to-many table between your albums and your performers --
    Code:
    CREATE TABLE album_performers
    ( album_id INTEGER NOT NULL
    , performer_id INTEGER NOT NULL
    , PRIMARY KEY ( album_id, performer_id )
    );
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    therefore, we can bypass many of the normal rules and conventions for database design in favour of simplifying the structure for ease of maintenance
    Sounds good to me.

    I have created that table.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rubble View Post
    I have created that table.
    you okay with what to put in it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I Now have this information in three of the tables:

    Code:
    INSERT INTO `album` (`album_id`, `title`, `size`, `source`) VALUES
    (1, 1, '10', 1);
    
    INSERT INTO `album_performers` (`album_id`, `performer_id`) VALUES
    (1, 1),
    (1, 3);
    
    INSERT INTO `performer` (`per_ID`, `full_name`) VALUES
    (1, 'Luciano Pavarotti '),
    (2, 'Enrico Caruso'),
    (3, 'Jose Carreras '),
    (4, 'Placido Domingo'),
    (5, 'Maria Callas'),
    (6, 'Joan Sutherland'),
    (7, 'Kiri Te Kanawa'),
    (8, 'Anna Netrebko');
    album has one row with the ID and a few other details
    performer has eight rows of ID's and performer names
    album_performers has two rows linking Luciano Pavarotti & Jose Carreras to album with the ID of 1

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    beauty!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    That seems straight forward; I think I was over complicating things in the first place.
    The first performer should come out first ( as the main performer ) on the list as long as I do not do a resort.

    I may have to split the album_performers table into two for side A and side B?

    This is my current query:
    Code:
    SELECT * FROM album
     
     JOIN 
     album_performers 
     ON 
     album.album_ID=album_performers.album_ID
     
     JOIN 
     source 
     ON 
     source.sou_ID=album.source 
     
     JOIN 
     performer 
     ON 
     performer.per_ID=album_performers.performer_ID
     
     WHERE album.album_ID = '1' ";
    And using print_r($row); my output is:
    Code:
    Array ( [album_id] => 1 [0] => 1 [title] => 1 [1] => 1 [size] => 10 [2] => 10 [source] => 1 [3] => 1 [4] => 1 [performer_id] => 1 [5] => 1 [sou_ID] => 1 [6] => 1 [origin] => C.P. Collection [7] => C.P. Collection [per_ID] => 1 [8] => 1 [full_name] => Luciano Pavarotti [9] => Luciano Pavarotti ) 
    1
    As you can see I only have one performer; but if I run the above SQL query in phpmy admin I get:
    Code:
    album_id Identifier
    
    title Track name side A
    
    size Record size
    
    source Source of the record
    
    album_id 
    
    performer_id 
    
    sou_ID 
    
    origin 
    
    per_ID 
    
    full_name 
     
    
    1 1 10 1 1 1 1 C.P. Collection 1 Luciano Pavarotti  
    1 1 10 1 1 3 1 C.P. Collection 3 Jose Carreras
    Any idea why I can not display all the artists?

  22. #22
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I was awake last night thinking about this and have been playing with the code all afternoon!

    The database is working well and I am getting what I want but I have a problem with the location on side A and Side B. Whatever I do I only end up with one location as the second overwrites the first. I have tried creating a LocationA and a LocationB but I can only get one $row['place']. Running the code through the SQL on phpmyadmin both locations are being picked up and when displaying the $row array with print_r() they are both there. I can get both locations if I do something like $row['27'] and $row['29'] but if I add something else to the database later I have to remember to change these two numbers.

    This is the output of print_r():
    Code:
    Array
    (
        [album_id] => 1
        [0] => 1
        [file_number] => 1111
        [1] => 1111
        [title] => 1
        [2] => 1
        [size] => 10
        [3] => 10
        [source] => 1
        [4] => 1
        [company_ID] => 3
        [5] => 3
        [location_A] => 1
        [6] => 1
        [location_B] => 2
        [7] => 2
        [matrix_A] => IRX456
        [8] => IRX456
        [matrix_B] => IRX789
        [9] => IRX789
        [speed_A] => 76
        [10] => 76
        [speed_B] => 78
        [11] => 78
        [date_A] => 1960-12-04
        [12] => 1960-12-04
        [date_B] => 1959-01-01
        [13] => 1959-01-01
        [catalogue] => 123_r5
        [14] => 123_r5
        [date_aq] => 1975-05-05
        [15] => 1975-05-05
        [purchase] => 5
        [16] => 5
        [est_value] => 10
        [17] => 10
        [sold] => 0
        [18] => 0
        [sou_ID] => 1
        [19] => 1
        [origin] => C.P. Collection
        [20] => C.P. Collection
        [comp_ID] => 3
        [21] => 3
        [name] => Hermes records
        [22] => Hermes records
        [album_ID] => 1
        [23] => 1
        [locationA_ID] => 1
        [24] => 1
        [locationB_ID] => 2
        [25] => 2
        [loc_ID] => 2
        [26] => 1
        [place] => Abbey road
        [27] => New York
        [28] => 2
        [29] => Abbey road
    )
    This is my query:
    Code:
    SELECT * FROM album
     
     JOIN 
     source 
     ON 
     source.sou_ID=album.source 
     
     JOIN 
     company 
     ON 
     company.comp_ID=album.company_ID
     
     JOIN 
     album_location 
     ON 
     album.album_ID=album_location.album_ID
     
     JOIN 
     location 
     ON 
     location.loc_ID=album_location.locationA_ID
     
     JOIN 
     location AS locB
     ON 
     locB.loc_ID=album_location.locationB_ID
     
     WHERE album.file_number = '1111'
    These are my tables:
    Code:
    CREATE TABLE IF NOT EXISTS `album` (
      `album_id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
      `file_number` int(5) NOT NULL,
      `title` int(4) NOT NULL COMMENT 'Track name side A',
      `size` enum('8','10','Other') NOT NULL COMMENT 'Record size',
      `source` int(4) NOT NULL COMMENT 'Source of the record',
      `company_ID` int(4) NOT NULL,
      `location_A` int(4) NOT NULL,
      `location_B` int(4) NOT NULL,
      `matrix_A` varchar(10) NOT NULL,
      `matrix_B` varchar(10) NOT NULL,
      `speed_A` int(6) NOT NULL COMMENT 'Speed side A',
      `speed_B` int(6) NOT NULL COMMENT 'Side B speed',
      `date_A` date NOT NULL COMMENT 'Date side A recorderd',
      `date_B` date NOT NULL COMMENT 'Date side B recorderd',
      `catalogue` varchar(12) NOT NULL COMMENT 'Catalogue number',
      `date_aq` date NOT NULL COMMENT 'Purchase date',
      `purchase` int(6) NOT NULL COMMENT 'Purchase price',
      `est_value` int(6) NOT NULL COMMENT 'Current estimated value',
      `sold` int(6) NOT NULL COMMENT 'Sold price',
      PRIMARY KEY (`album_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
    
    
    CREATE TABLE IF NOT EXISTS `album_location` (
      `album_ID` int(11) NOT NULL,
      `locationA_ID` int(11) NOT NULL,
      `locationB_ID` int(11) NOT NULL,
      PRIMARY KEY (`album_ID`,`locationA_ID`,`locationB_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    
    CREATE TABLE IF NOT EXISTS `location` (
      `loc_ID` smallint(4) NOT NULL AUTO_INCREMENT,
      `place` varchar(50) NOT NULL DEFAULT 'Place',
      PRIMARY KEY (`loc_ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=3 ;

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rubble View Post
    Whatever I do I only end up with one location as the second overwrites the first.
    that's a php issue, but it's due to the fact that you have two columns in the query result with the same name

    solution: stop using the dreaded, evil "select star"

    list the columns you want explicitly, using column aliases as necessary --
    Code:
    SELECT source.foo
         , company.bar
         , locA.name AS location_A
         , locB.name AS location_B
      FROM album
    INNER 
      JOIN source 
        ON source.sou_ID = album.source 
    INNER
      JOIN company 
        ON company.comp_ID = album.company_ID
    INNER 
      JOIN album_location 
        ON album_location.album_ID = album.album_ID
    INNER 
      JOIN location AS locA
        ON locA.loc_ID = album_location.locationA_ID
    INNER 
      JOIN location AS locB
        ON locB.loc_ID = album_location.locationB_ID
     WHERE album.file_number  =  '1111'
    Last edited by r937; Nov 2, 2013 at 11:24. Reason: typo
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    Looks like I am up and running but need to go back and change some of my variable, column names etc. as they are a bit of a mess.

    I also need to modify my data input page and its onto the update page

    Thank you very much for the help r937


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
  •