SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: 3 tables join

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    3 tables join

    Hi everyone.

    I have problems with query I'm doing for gallery

    We have 2 main tables gallery for keeping detailed information about gallery, table fonti where we keep all the images and table galobj wich connects gallery to specific fonti.

    Ok for now it's no problem but we have fourth table formati. Every fonti (picture) can have one or more formats

    Asume we have galery_id so we dont need to look at the gallery table.

    Code:
    CREATE TABLE "galobj" (
      "sg_id" INTEGER, 
      "fnt_id" INTEGER, 
      "iorder" INTEGER, 
      "desc" TEXT, 
      "desc_small" TEXT
    );
    
    
    CREATE TABLE "fonti" (
      "fnt_id" INTEGER NOT NULL, 
      "fnt_type" VARCHAR(16), 
      "fnt_title" TEXT, 
      "fnt_subtitle" TEXT, 
      "fnt_author" VARCHAR(128), 
      "fnt_copyright" TEXT, 
      "fnt_extra" TEXT, 
      "fnt_copyfree" BOOLEAN, 
      "must_check_formats" BOOLEAN DEFAULT false, 
      CONSTRAINT "fonti_pkey" PRIMARY KEY("fnt_id")
    )
    
    
    CREATE TABLE "formati" (
      "fnt_id" INTEGER, 
      "frmt_id" INTEGER, 
      "frmt_subtype" VARCHAR(16), 
      "frmt_url" TEXT, 
      "frmt_size" INTEGER, 
      "frmt_extra1" INTEGER, 
      "frmt_extra2" INTEGER
    ) ;
    So now i have sg_id (gallery_id) I want to get all the pictures format (btw: format fields are 'frmt_extra1' [width] and 'frmt_extra2'[height]) 400 x 400?

    I am trying all combinations but every time i get duplicate items, no way to eliminate them

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please show your query, and i'll fix it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM formati fr 
    INNER JOIN fonti fnt ON (fnt.fnt_id = fr.fnt_id)
    INNER JOIN galobj g ON (g.fnt_id = fnt.fnt_id)
    WHERE fr.frmt_subtype = 'jpeg' 
    AND (fr.frmt_extra1 >= '100' AND fr. frmt_extra1 <= '500') 
    AND (fr.frmt_extra2 >= '100' AND fr. frmt_extra2 <= '500') 
    AND g.sg_id = 8512478
    This should be it. I'am joining 3 tables because i need data from all tables selected.

    I can attachyou results if things would be clear . You can se that for one fnt_id i have couple of frmt_id's i need just one, criteria should be the highest one but realy just one, it doesn't matter

    Thank you so much
    Attached Files Attached Files

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by djomla View Post
    i need just one, criteria should be the highest one
    picking the latest for each group is a common requirement

    you can see how it is done by joining to a subquery
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star 
      FROM galobj AS g
    INNER
      JOIN fonti AS fnt
        ON fnt.fnt_id = g.fnt_id
    INNER
      JOIN ( SELECT fnt_id
                  , MAX(frmt_id) AS latest
               FROM formati 
              WHERE frmt_subtype = 'jpeg' 
                AND frmt_extra1 >= 100 AND frmt_extra1 <= 500 
                AND frmt_extra2 >= 100 AND frmt_extra2 <= 500
             GROUP
                 BY fnt_id ) AS m
        ON m.fnt_id = g.fnt_id
    INNER
      JOIN formati AS fr
        ON fr.fnt_id = g.fnt_id 
       AND fr.frmt_id = m.latest
     WHERE g.sg_id = 8512478
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!! thank you!! thank you!!

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star 
      FROM galobj AS g
    INNER
      JOIN fonti AS fnt
        ON fnt.fnt_id = g.fnt_id
    INNER
      JOIN ( SELECT fnt_id
                  , MAX(frmt_id) AS latest
               FROM formati 
              WHERE frmt_subtype = 'jpeg' 
                AND frmt_extra1 >= 100 AND frmt_extra1 <= 500 
                AND frmt_extra2 >= 100 AND frmt_extra2 <= 500
             GROUP
                 BY fnt_id ) AS m
        ON m.fnt_id = g.fnt_id
    INNER
      JOIN formati AS fr
        ON fr.fnt_id = g.fnt_id 
       AND fr.frmt_id = m.latest
     WHERE g.sg_id = 8512478
    I found a 'bug' in this query ... this part is returning wrong :

    Code:
    SELECT fnt_id
                  , MAX(frmt_id) AS latest
               FROM formati 
              WHERE frmt_subtype = 'jpeg' 
                AND frmt_extra1 >= 100 AND frmt_extra1 <= 500 
                AND frmt_extra2 >= 100 AND frmt_extra2 <= 500
             GROUP
                 BY fnt_id
    It assumes that the last frmt_id is the right format but acctually in this case it returns for each fnt_id 3 frmt_id and the second one is the right one, but then again not every time. I have to chech frmt_extra1 and frmt_extra2 look :


    Code:
    fnt_id   frmt_id  frmt_extra1 frmt_extra2 
    10956271 10956299 1024  	576 
    10956271 10956302  550  	413 
    10956271 10956314  650  	366 
    10956235 10956238 1024  	576 
    10956235 10956240  550  	413 
    10956235 10956252  650  	366 
    10956266 10956269 1024  	576 
    10956266 10956272  550  	413 
    10956266 10956284  650  	366 
    10956470 10956657 1024  	576 
    10956470 10956659  550  	413 
    10956470 10956671  650  	366
    In this case i was searching if frmt_extra1 or frmt_extra2 has 550 if not get bigger. So for this he returns me this format :

    10956271 10956299 1024 576

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, i have no idea what you just said
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok i will try to explain better :

    This is the query :

    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star 
      FROM galobj AS g
    INNER
      JOIN fonti AS fnt
        ON fnt.fnt_id = g.fnt_id
    INNER
      JOIN ( SELECT fnt_id
                  , MAX(frmt_id) AS latest
               FROM formati 
              WHERE frmt_subtype = 'jpeg' 
                AND frmt_extra1 >= 550 AND frmt_extra1 <= 650
                AND frmt_extra2 >= 550 AND frmt_extra2 <= 650
             GROUP
                 BY fnt_id ) AS m
        ON m.fnt_id = g.fnt_id
    INNER
      JOIN formati AS fr
        ON fr.fnt_id = g.fnt_id 
       AND fr.frmt_id = m.latest
     WHERE g.sg_id = 8512478
    He returns formats this :

    Code:
    fnt_id        frmt_id     frmt_extra1    frmt_extra2
    10956271 10956299   1024              576
    But he should return the nearest format available look :

    Code:
    fnt_id   frmt_id  frmt_extra1 frmt_extra2 
    10956271 10956299 1024  	576 
    10956271 10956302  550  	413 
    10956271 10956314  650  	366 
    10956235 10956238 1024  	576 
    10956235 10956240  550  	413 
    10956235 10956252  650  	366 
    10956266 10956269 1024  	576 
    10956266 10956272  550  	413 
    10956266 10956284  650  	366 
    10956470 10956657 1024  	576 
    10956470 10956659  550  	413 
    10956470 10956671  650  	366
    Instead he is returning only first for fnt_id. Not sorting by frmt_extra1 and frmt_extra2.

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Are you looping through the result set with whatever server-side language your using? If you are try running the query through phpmyadmin (or the appropriate equivalent for whatever sever-side language your using
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •