SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT a list of words with commas in SQL from a column?

    I have a column called fruit_type which contains text values like:

    Row 1: apples, oranges, bananas
    Row 2: bananas
    Row 3: oranges, bananas
    Row 4: apples
    Row 5: NULL

    I am trying to write an sql statement so that I get grouped/distinct results like:

    apples
    oranges
    bananas

    If I run the following query:

    SELECT fruit_type
    FROM fruits
    WHERE fruit_type <> ''
    ORDER BY fruit_type ASC

    ...I get:

    apples
    apples, oranges, bananas
    bananas
    oranges, bananas

    Any help would be appreciated, thanks.

  2. #2
    SitePoint Addict liquidautumn's Avatar
    Join Date
    Nov 2002
    Location
    Kharkov, Ukraine
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the problem you experiencing coming from badly designed database
    Right way to implement 1 to many relationship in RDBMS is using reference table, for example

    Table "FruitType":

    id caption
    1 apples
    2 oranges
    3 bananas

    Table "EntryTable"
    id caption
    1 Entry 1
    2 Entry 2
    3 Entry 3

    Table "ReferenceTable"
    entry_id type_id
    1 1
    1 2
    1 3
    2 3
    3 2
    3 3

    Where each entry associated with as many types as you need. There would be useful PRIMARY KEY on both columns, but such details you may learn by yourserf, reading things on Database Normalization and "Implementing One to Many Relationship" (google will help you)

    to choose all types you may do "SELECT * FROM FruitType WHERE 1"
    to choose all entities of type orange you may do:

    "SELECT * FROM ReferenceTable AS r LEFT JOIN EntryTable AS e ON e.id=r.entry_id where r.type_id=2"

    hope this helps

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would love to split that column to make the design work in a relational way, but unfortunately I can't because I get a database dump at weekly intervals and the tables are overwritten. We just havent got the time to keep on recreating extra tables.

    Is there no way to do this with one sql statement, on the one table?

    The database is for a web application and we utilise ASP. Maybe some of the processing could be done with ASP instead of a complex sql statement?

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    liquidautumn, nice explanation, except your LEFT JOIN example should be an INNER join instead

    redcoupe, i would still strongly suggest that you convert the incoming table to a properly normalized table right after the dump

    if you're going to be using this data in a web app, you don't want to have to do a table scan every time you query the data

    you can easily set up a series of sql statements that will convert the data painlessly, and these steps should be a part of the weekly dump cycle

    then your query will be trivially easy to write and lightning fast, too

    i'm sorry, i don't buy the "we just havent got the time to keep on recreating extra tables" argument

    apparently you have time to cope with the effects of a slow web application?

    note that shifting some of the processing to asp is not a solution, because you will still have to do a table scan for every query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict liquidautumn's Avatar
    Join Date
    Nov 2002
    Location
    Kharkov, Ukraine
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as far as i know the difference between INNER and NATURAL (LEFT or RIGHT) JOIN is that INNER JOIN not selecting rows from the 1st table, where no corresponding row in a table joined. Actually you right, is there is orphant records in a reference table, it's better to not include them in result. I just wondering, assuming, i am keeping data consistent, using cascade deletes (not speaking of Mysql, i'm a PgSQL adept , is there any other reason to choose INNER JOIN?

  6. #6
    SitePoint Addict liquidautumn's Avatar
    Join Date
    Nov 2002
    Location
    Kharkov, Ukraine
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    should say, you explained need of database normalization just brilliantly, btw

  7. #7
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im taking all your comments on board about using multiple tables. Any ideas on the query syntax to create and populate a distinct fruit_type table and the linker table?

    Thanks again for your help and suggestions, most appreciated.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    liquidautumn, consider the difference between this --
    Code:
    SELECT * FROM t1 LEFT JOIN t2 
    ON t1.id = t2.t1_id AND t2.foo = 'bar'
    and this --
    Code:
    SELECT * FROM t1 LEFT JOIN t2 
    ON t1.id = t2.t1_id WHERE t2.foo = 'bar'
    you will find that the latter of these is actually an inner join because it can never return unmatched rows from t1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedCoupe
    Any ideas on the query syntax to create and populate a distinct fruit_type table and the linker table?
    yes, you could do it with a series of moderately complex queries, but you could also to it with ASP and it would probably be a bit easier for you -- what's your preference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it may be a tad easier to run as asp. Then when the dump import has finished, I can just run an asp page that would hopefully do all the hard work

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that'd be the way i'd do it too (except i'd do it in coldfusion )

    do a SELECT, then loop over the results, and for each row, pull the comma-separated list out and loop over that, creating an INSERT VALUES for each non-null

    note some database like mysql will allow you to generate this --
    Code:
    insert into rowfruits values 
    (1,'apples'),(1,'oranges'),(1,'bananas')
    ;
    insert into rowfruits values 
    (2,'bananas')
    ;
    insert into rowfruits values 
    (3,'oranges'),(3,'bananas')
    ;
    insert into rowfruits values 
    (4,'apples')
    ;
    but some databases like sql server require you to generate this --
    Code:
    insert into rowfruits values 
    (1,'apples')
    insert into rowfruits values 
    (1,'oranges')
    insert into rowfruits values 
    (1,'bananas')
    
    insert into rowfruits values 
    (2,'bananas')
    
    insert into rowfruits values 
    (3,'oranges')
    insert into rowfruits values 
    (3,'bananas')
    
    insert into rowfruits values 
    (4,'apples')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Using an ASP script to loop through the records and run inserts, I have now created a new table containing all the info required. It only takes a few seconds to split out all the values.

    Thanks for your help, it is much faster than the alternative.


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
  •