SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast that_tim_fella's Avatar
    Join Date
    Feb 2003
    Location
    England
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    2 Tables, no duplicates

    This is probably really easy for all you experts!

    Code:
    CREATE TABLE `colours` (
       `colours_id` int(11) NOT NULL auto_increment,
       `colours_name` varchar(64) NOT NULL default '',
       `colours_swatch` varchar(64) default NULL,
       `colours_classification` varchar(64) default NULL,
       `colours_safety` varchar(64) default NULL,
       PRIMARY KEY (`colours_id`)
     )
    Code:
    CREATE TABLE `colours_application` (
       `colours_application_id` int(11) NOT NULL default '0',
       `colours_application_application` varchar(64) NOT NULL default ''
     )
    colours_application will have multiple entries based on the colours_id/colours_application_id. So, colours_id '1' might have 10 entries in the second table under colours_application_id '1'. Hope this is clear.

    I can't work out how to pull unique rows. So if I select* from the colours table and colours_application_application from the colours_application where both ..._id = '1', I would get 10 rows. I'd like them aggregated into 1 row.

    Many thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not clear, sorry

    how about showing us a few sample rows of detail data, and sample rows of what you want to get back from the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast that_tim_fella's Avatar
    Join Date
    Feb 2003
    Location
    England
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies. Hope this helps.

    Code:
    Table colours
    
    colours_id ### colours_name ### colours_swatch ### colours_classification ### colours_safety
    
    1 ### Ceraset Blue 2R ### ACID BLUE 2R.gif ### Acid (Ceraset) ### (S) Ceraset Blue 2R.pdf
    2 ### Ceralan Fast ### ACID RED 213.gif ### Acid Premet (1:2 & 1:1) ### (S) Ceralan Fast Bordeaux FRL.pdf
    Code:
    Table colours_application
    
    colours_application_id ### colours_application_application
    
    1 ### wool
    1 ### silk
    1 ### acrylic
    2 ### nylon
    2 ### rayon
    Desired output would be:

    Code:
    colours_id ### colours_name ### colours_swatch ### colours_classification ### colours_safety ### colours_application_application
     
    1 ### Ceraset Blue 2R ### ACID BLUE 2R.gif ### Acid (Ceraset) ### (S) Ceraset Blue 2R.pdf ### wool - silk - acrylic
    2 ### Ceralan Fast ### ACID RED 213.gif ### Acid Premet (1:2 & 1:1) ### (S) Ceralan Fast Bordeaux FRL.pdf ### nylon - rayon

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, unique rows

    if you are lucky enough to be on 4.1 there is the GROUP_CONCAT()
    function, explained in 13.9.1 GROUP BY Functions

    otherwise, you will have to do the "denormalization" yourself
    in some kind of scripting language that can take the results
    of the query an process it sequentially

    the query will be a simple join, and will return multiple rows
    per colour, and you will essentially have to take the contents
    of the colours_application_application column and turn it into a
    list, i.e. multiple values into one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast that_tim_fella's Avatar
    Join Date
    Feb 2003
    Location
    England
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your replies r937, it's a great help.

    I already had the solution worked out using simpler sql and php, but I thought there would be an easy way to do this just with the sql. I seem to remember doing that kind of thing with Postgres or Ingres, but that's a loooooong time ago so I might have dreamt it!


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
  •