SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jul 2003
    Location
    Toronto, Ontario
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Compress Duplicate Entries?

    Hey guys,

    I feel bad for posting so many question. Hopefully these question can help out others that are just starting out with SQL.

    So I'm retrieving a Value from Name_01 & Name_02 using...

    Code:
    SELECT Name_01, Name_02 FROM Companies
    However Name_01 & Name_02 may contain the same value. ex. Mike Smith

    So when I pull the entries I would like to omit the duplicates. Is there anyway of going about this?

    Thanks,
    Mario
    $ Available For Hire
    Need a website designed or coded? Then feel free to contact me!

    PSP Backgrounds - PSPBG.net || Photoshop Brushes - PSBrushes.net

  2. #2
    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)
    what is the actual definition of each of those columns? i mean, what are they for? why do you need two name columns?

    and in the given example, where both columns are 'Mike Smith', please show what results you'd like to return with the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2003
    Location
    Toronto, Ontario
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically each company can have up to 12 directors.

    So each company has Director_01, Director_02 etc.

    However, more then one company can have the same director. These are not always under the same column (Director_01, Director_02)

    So for instance...

    Company 01

    Director_01 - Mike Smith
    Director_02 - Bob Jones


    Company 02

    Director_01 - Judy Red
    Director_02 - Mike Smith

    Now when I retrieve all of the Directors from the companies Mike Smith would appear twice, even though it is the same person.

    I hope that clears things up a bit more.

    Thank you for taking the time to look over my threads.
    $ Available For Hire
    Need a website designed or coded? Then feel free to contact me!

    PSP Backgrounds - PSPBG.net || Photoshop Brushes - PSBrushes.net

  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)
    you srsly need to redesign this table

    remove the Director_01, Director_02 etc columns from the companies table

    create a new table, CompanyDirectors, with two columns: company_id and director (these two columns, together, form the primary key)

    then your query becomes much simpler
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jul 2003
    Location
    Toronto, Ontario
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the response, after an hour of searching around and trying to combine arrays I think you may be correct :-P

    I'm just having a hard time visualizing how this would work.

    I currently have an input form here the admin can submit a company. There he can list 12 directors for that company.

    When he hits submit it would have to check against the CompanyDirectors, if they dont exist it would add the director.

    The part I find hard is, how would the 12 Director ID's be associated with the Companies?

    Would I have a column under Companies called Director ID where I would have a list of the 12 Director ID's?
    $ Available For Hire
    Need a website designed or coded? Then feel free to contact me!

    PSP Backgrounds - PSPBG.net || Photoshop Brushes - PSBrushes.net

  6. #6
    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)
    each director would be in a separate row of the CompanyDirectors table

    there is no Director column in the Companies table

    the company_id column in CompanyDirectors would link back to the Companies table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jul 2003
    Location
    Toronto, Ontario
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WAIT! I think it came to me.

    I was thinking that you can only have the same Director name once in that table, however if you have diff ID's you can have the same director multiple times.

    The only other concern I have is, while Mark Smith may be in the table twice with different Company ID's will it be hard to sort it out so what when I call for a list of the Directors he won't show up twice?

    Thanks,
    Mario
    $ Available For Hire
    Need a website designed or coded? Then feel free to contact me!

    PSP Backgrounds - PSPBG.net || Photoshop Brushes - PSBrushes.net

  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)
    Quote Originally Posted by Ethics View Post
    The only other concern I have is, while Mark Smith may be in the table twice with different Company ID's will it be hard to sort it out so what when I call for a list of the Directors he won't show up twice?
    SELECT DISTINCT director FROM CompanyDirectors

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jul 2003
    Location
    Toronto, Ontario
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AH! perfect, you saved me hours of frustration.

    I guess that means we're up to 2 beers :-P

    Time to go and figure out and recode the backend input form.

    Thanks again,
    Mario
    $ Available For Hire
    Need a website designed or coded? Then feel free to contact me!

    PSP Backgrounds - PSPBG.net || Photoshop Brushes - PSBrushes.net


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
  •