SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Seriph's Avatar
    Join Date
    Oct 2005
    Location
    San Diego, CA
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Some kind of JOIN ?

    I'm working on a label inventory tracker, and this situation came up...
    I have 3 tables, `labels`, `sites` and `quantity`

    Code:
    labels     sites        quantity
    ------     --------     -----------
    id         id           label_id
    prod_id    name         site_id
    name       info         number
    img                     updated
    Is there a way to build the following result
    first row is headers
    second row is name from label table, then quantites from the respective row from the quantity table each represeting a different site and a total

    The number of sites someone may have, is varible.

    Code:
    label_name  |  office   |  manufacturer  |  site2  | total
    ----------------------------------------------------------
    Label 42    |  30       |  20            |  40     | 90
    ----------------------------------------------------------
    Is there a way to do this? Is it the wrong way in the first place?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    first write a query that will return your results like this:
    Code:
    label 42,office,30
    label 42,manufacturer,20
    label 42,site2,40
    then i will show you how to rewrite it to get the horizonalness.

    or, if you want to try jumping to the final query yourself, i'll give you a hint: it will require three left joins (office, manufacturer, site2)

  3. #3
    SitePoint Zealot Seriph's Avatar
    Join Date
    Oct 2005
    Location
    San Diego, CA
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK got this..

    Code:
    SELECT l.name as name, s.name as site, q.number FROM `labels` AS l 
    JOIN quantity AS q ON q.label_id = l.id 
    JOIN sites AS s ON s.id = q.site_id
    WHERE l.id = 1
    Works fine, whats that magic that lets me have multiple sites, and put them in columns?' Remember, the number of sites is subject to change...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Seriph
    Remember, the number of sites is subject to change...
    in that case, any attempt to kluge the columns is doomed to failure

    one surefire, foolproof, painless way to do it easily, however, is to set up linked tables to your mysql database using microsoft access, which has crosstab query capability built in

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

  5. #5
    SitePoint Zealot Seriph's Avatar
    Join Date
    Oct 2005
    Location
    San Diego, CA
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol I geuss I'll stick with the regular join. Thanks guys

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, i didn't notice that you hinted at variable columns in your first post. yes, this is doomed without some magic from your host language.


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
  •