SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict Bloodfest's Avatar
    Join Date
    Feb 2006
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Statement Help!

    Ok, I have 1 table containing partnumbers "tbl_parts" and another table containing seperate serialnumbers "tbl_serials" of each part. (1-N)

    Using 1 SQL statement I'd like to get this result:

    PartNumber Nomenclaute SerialNumbers
    abcdef somepart 001,002,003

    Is this possible, I basically want to create a field with the serialnumbers in list format, grouping on partnumber.

  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)
    look up GROUP_CONCAT in da manual, it is exactly what you want

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

  3. #3
    SitePoint Addict Bloodfest's Avatar
    Join Date
    Feb 2006
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I gave it a try.. got results but it only gives me back the first record.

    Code:
    select *, s.id as serial_id, p.id as part_id, group_concat(s.serialnumber) as serials from inventory_parts p
    join inventory_serials s on s.part_id = p.id
    join inventory_locations l on s.location_id = l.id
    join devices d on d.id = l.device_id

  4. #4
    SitePoint Addict Bloodfest's Avatar
    Join Date
    Feb 2006
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it, your awsome... I had to add group by partnumber..


  5. #5
    SitePoint Addict Bloodfest's Avatar
    Join Date
    Feb 2006
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the group by partnumber accually messed up my original result page.

    partnumber nomenclature

    abcdef somepart
    - 0001
    - 0002
    - 0003

    need to list some info about the serialnumber under each partnumber..

    make sense?

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you would handle that in your front end application. use the database to retrieve/manipulate data, use your front end layer to display it.

  7. #7
    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 Bloodfest View Post
    need to list some info about the serialnumber under each partnumber..
    in that case, GROUP BY is ruled out
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •