SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't figure out 3 table query

    Ok. I have a DB and I am trying to retrieve the following:

    table1.*
    table2.valueX
    table3.valueY

    The relationships between the tables are:

    table1.value1 = table2.value1
    table2.value2 = table3.value2

    I have been looking up everything I can find and have tried all kinds of join queries and nothing is working. Any help would be greatly appreciated!!!

    Thanks!

  2. #2
    SitePoint Addict battra's Avatar
    Join Date
    Oct 2004
    Location
    Asylum
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you get error message or just empty results? Posting your actual code/query will be useful. Also make sure the you have common data between table1.value1 - table2.value1, and between table2.value2 - table3.value2.

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All you need is to rewrite your post slightly

    Code:
    -- I have a DB and I am trying to retrieve the following:
    SELECT 
    table1.*,
    table2.valueX,
    table3.valueY
    
    FROM  table1, table2, table3
    
    -- The relationships between the tables are:
    
    WHERE 
    table1.value1 = table2.value1
    AND
    table2.value2 = table3.value2
    If this won't work for you, please post exact table structure and sample data.

  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)
    even better would be to learn the standard way of doing it, because there are several advantages, one of which is that it isolates the join conditions and therefore makes the whole query easier to understand, and therefore to maintain
    Code:
    select table1.*
         , table2.valueX
         , table3.valueY
      from table1
    inner
      join table2
        on table1.value1 = table2.value1
    inner
      join table3
        on table2.value2 = table3.value2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried the suggestion by both stereofrog and r937, and I have had bad results from both.

    Here is the real-life query:

    Code:
    SELECT Listings_Table .  * , 
     Organizations_Table.Phone_Area, 
     Organizations_Table.Phone_Number, 
     Agents_Table.Website
     FROM Listings_Table
     INNER 
     JOIN Organizations_Table ON Listings_Table.LISTING_OFFICE = Organizations_Table.Org_Name
     INNER 
     JOIN Agents_Table ON Organizations_Table.Org_ID = Agents_Table.Org_ID
    The problem I'm having is that it returns 17 duplicates of each row.

    I did not design the DB, nor have any control over it's structure.

  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)
    i'm sorry you have had "bad results" but i am confident that my query was not at fault



    if each listing is unique, then i'm willing to bet that you did not get "17 duplicates of each row"

    however, you may have gotten 17 slightly different rows for the same listing

    that's not at all the same thing, is it

    a duplicate row is the same in every column of the row

    you probably got all the agents for every organization linked to each listing

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

  7. #7
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937:

    I'm not trying to blame you. But yes, the "results" as in the actual fields returned are identical. Nevertheless, I think you are right that a single row is being returned for each agent listed in the organization - although the fields returned would not indicate that.

    I thought the join would work basically like, Find all rows where L.LISTING_OFFICE = O.Org_Name && O.Org_ID = A.Org_ID where the matching O.Org_Name and O.Org_ID would be from the same row that matched both L.LISTING_OFFICE and A.Org_ID - but it obviously isn't working that way. Is it possible to achieve those results using the given fields - or do I need to join the Listings_Table to the Agents_Table instead of the Organizations_Table to the Agents_Table?

  8. #8
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. This query appears to return good results:

    Code:
    SELECT Listings_Table .  * , 
      Organizations_Table.Phone_Area, 
      Organizations_Table.Phone_Number, 
      Agents_Table.Website
      FROM listings_table
      INNER 
      JOIN organizations_table ON listings_table.LISTING_OFFICE = organizations_table.Org_Name
      INNER 
      JOIN agents_table ON ( Listings_Table.AGENT = CONCAT( Agents_Table.Last_Name,  ', ', Agents_Table.first_Name )  )
    The problem now is that it is ridiculously slow.

    Instead of doing a join, performance wise would there be any benefit to breaking this up into two seperate queries?

  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)
    "instead of doing a join" -- don't even think about it

    what you should be doing here, given that you did not design the database and have no control over its structure, is to first of all explain that structure to us so that we can help you with the correct join

    what are the actual datatypes of the fields involved in the query?

    can you give a few sample rows from each table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    can you give a few sample rows from each table?
    Here are the table structures:

    Code:
    CREATE TABLE `agents_table` (
        `User_ID` int(25) NOT NULL default '0',
        `Org_ID` int(25) default NULL,
        `First_Name` varchar(255) collate latin1_general_ci default NULL,
        `Last_Name` varchar(255) collate latin1_general_ci default NULL,
        `Email` varchar(255) collate latin1_general_ci default NULL,
        `Website` varchar(255) collate latin1_general_ci default NULL,
        `Code` int(11) NOT NULL default '0'
      )
      
      CREATE TABLE `organizations_table` (
        `Org_ID` int(25) NOT NULL default '0',
        `Org_Name` varchar(255) collate latin1_general_ci default NULL,
        `Contact` varchar(255) collate latin1_general_ci default NULL,
        `Phone_Desc` varchar(255) collate latin1_general_ci default NULL,
        `Phone_Area` varchar(255) collate latin1_general_ci default NULL,
        `Phone_Number` varchar(255) collate latin1_general_ci default NULL
      )
      
      CREATE TABLE `listings_table` (
        `MLS` int(30) NOT NULL default '0',
        `ASKING_PRICE` int(10) default NULL,
        `CITY` varchar(25) collate latin1_general_ci default NULL,
        `ZIP` varchar(5) collate latin1_general_ci default NULL,
        `BEDROOMS` varchar(30) collate latin1_general_ci default NULL,
        `BATHS` varchar(30) collate latin1_general_ci default NULL,
        `HALF_BTH` varchar(30) collate latin1_general_ci default NULL,
        `LIV_AREA` varchar(30) collate latin1_general_ci default NULL,
        `CONSTRUC` varchar(30) collate latin1_general_ci default NULL,
        `GARAGE` varchar(30) collate latin1_general_ci default NULL,
        `FIREPLC` varchar(30) collate latin1_general_ci default NULL,
        `POOL` varchar(30) collate latin1_general_ci default NULL,
        `CARPORT` varchar(30) collate latin1_general_ci default NULL,
        `ACRES` varchar(10) collate latin1_general_ci default NULL,
        `Year_Built` varchar(10) collate latin1_general_ci default NULL,
        `AGENT` varchar(30) collate latin1_general_ci default NULL,
        `LISTING_OFFICE` varchar(40) collate latin1_general_ci default NULL,
        `SUBDIV` varchar(25) collate latin1_general_ci default NULL,
        `COUNTY` varchar(25) collate latin1_general_ci default NULL,
        `LIVABLE_SQUARE_FEET` varchar(10) collate latin1_general_ci default NULL,
        `SOURCE` varchar(30) collate latin1_general_ci default NULL,
        `HOA` varchar(25) collate latin1_general_ci default NULL,
        `ELEM_SCH` varchar(30) collate latin1_general_ci default NULL,
        `JR_HIGH` varchar(30) collate latin1_general_ci default NULL,
        `HIGH_SCH` varchar(30) collate latin1_general_ci default NULL,
        `CASH` varchar(10) collate latin1_general_ci default NULL,
        `FHA` varchar(10) collate latin1_general_ci default NULL,
        `VA` varchar(10) collate latin1_general_ci default NULL,
        `CONVENTIONAL` varchar(10) collate latin1_general_ci default NULL,
        `ASSUME_NONQUAL` varchar(10) collate latin1_general_ci default NULL,
        `ASUM_NQ_SELR_AP` varchar(10) collate latin1_general_ci default NULL,
        `ASSUME_QUAILIFY` varchar(10) collate latin1_general_ci default NULL,
        `OWNER_FINANCE` varchar(10) collate latin1_general_ci default NULL,
        `OWNER_CARRY` varchar(10) collate latin1_general_ci default NULL,
        `APPOINT_ONLY` varchar(10) collate latin1_general_ci default NULL,
        `CALL_LIST_OFC` varchar(10) collate latin1_general_ci default NULL,
        `KEYSAFE_LB_CALL` varchar(10) collate latin1_general_ci default NULL,
        `CALL_OWNR` varchar(10) collate latin1_general_ci default NULL,
        `LISTING_AGT_CO` varchar(10) collate latin1_general_ci default NULL,
        `ALARM_ACTIVATED` varchar(10) collate latin1_general_ci default NULL,
        `SLAB` varchar(10) collate latin1_general_ci default NULL,
        `PIER_BEAM` varchar(10) collate latin1_general_ci default NULL,
        `TWO_STORY` varchar(10) collate latin1_general_ci default NULL,
        `TRI_LEVEL` varchar(10) collate latin1_general_ci default NULL,
        `BASEMENT` varchar(10) collate latin1_general_ci default NULL,
        `STUCCO` varchar(10) collate latin1_general_ci default NULL,
        `ASBESTOS` varchar(10) collate latin1_general_ci default NULL,
        `METAL_SIDING` varchar(10) collate latin1_general_ci default NULL,
        `VINYL_SIDING` varchar(10) collate latin1_general_ci default NULL,
        `CONCRETE_BLOCK` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_SIDING` varchar(10) collate latin1_general_ci default NULL,
        `BRICK_VENEER` varchar(10) collate latin1_general_ci default NULL,
        `FRAME` varchar(10) collate latin1_general_ci default NULL,
        `WOOD_MASONRY` varchar(10) collate latin1_general_ci default NULL,
        `CENTRAL` varchar(10) collate latin1_general_ci default NULL,
        `ZONED` varchar(10) collate latin1_general_ci default NULL,
        `GAS` varchar(10) collate latin1_general_ci default NULL,
        `ELECTRIC` varchar(10) collate latin1_general_ci default NULL,
        `LP_GAS` varchar(10) collate latin1_general_ci default NULL,
        `HEAT_PUMP_TWO` varchar(10) collate latin1_general_ci default NULL,
        `FLOOR_FURNACE` varchar(10) collate latin1_general_ci default NULL,
        `WALL_FURNANCE` varchar(10) collate latin1_general_ci default NULL,
        `SPACE_HEATER` varchar(10) collate latin1_general_ci default NULL,
        `BASEBOARD` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_HEAT` varchar(10) collate latin1_general_ci default NULL,
        `NONE_HEAT` varchar(10) collate latin1_general_ci default NULL,
        `CENTRAL_AIR` varchar(10) collate latin1_general_ci default NULL,
        `ZONED_AIR` varchar(10) collate latin1_general_ci default NULL,
        `ELECTRIC_AIR` varchar(10) collate latin1_general_ci default NULL,
        `HEAT_PUMP_AIR` varchar(10) collate latin1_general_ci default NULL,
        `EVAP` varchar(10) collate latin1_general_ci default NULL,
        `WINDOW_UNITS` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_AIR` varchar(10) collate latin1_general_ci default NULL,
        `NONE_AIR` varchar(10) collate latin1_general_ci default NULL,
        `FORMAL_LIVING` varchar(10) collate latin1_general_ci default NULL,
        `DEN` varchar(10) collate latin1_general_ci default NULL,
        `GAME_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `STUDY_OFFICE` varchar(10) collate latin1_general_ci default NULL,
        `GARDEN_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `BONUS_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `BALCONY_LOFT` varchar(10) collate latin1_general_ci default NULL,
        `ENCLOSD_POR_PAT` varchar(10) collate latin1_general_ci default NULL,
        `SEQUESTERD_MSTR` varchar(10) collate latin1_general_ci default NULL,
        `IN_LAW_PLAN` varchar(10) collate latin1_general_ci default NULL,
        `GROUND_FL_MSTR` varchar(10) collate latin1_general_ci default NULL,
        `ATRIUM` varchar(10) collate latin1_general_ci default NULL,
        `SUN_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `ENCLOSED_PATIO` varchar(10) collate latin1_general_ci default NULL,
        `HOBBY_SHOP` varchar(10) collate latin1_general_ci NOT NULL default '',
        `OTHER_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `INSIDE_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `KITCHEN` varchar(10) collate latin1_general_ci default NULL,
        `GARAGE_TWO` varchar(10) collate latin1_general_ci default NULL,
        `CLOSET` varchar(10) collate latin1_general_ci default NULL,
        `DRYER_ELEC` varchar(10) collate latin1_general_ci default NULL,
        `DRYER_GAS` varchar(10) collate latin1_general_ci default NULL,
        `WASHER_CONNECT` varchar(10) collate latin1_general_ci default NULL,
        `NONE_WASHER` varchar(10) collate latin1_general_ci default NULL,
        `W_CURTAINS_ALL` varchar(10) collate latin1_general_ci default NULL,
        `PT_DRAPES_CURTN` varchar(10) collate latin1_general_ci default NULL,
        `WOOD_SHUTTERS` varchar(10) collate latin1_general_ci default NULL,
        `SHADES_BLINDS` varchar(10) collate latin1_general_ci default NULL,
        `CARPETING` varchar(10) collate latin1_general_ci default NULL,
        `VINYL_FLOORING` varchar(10) collate latin1_general_ci default NULL,
        `TILE_FLOORING` varchar(10) collate latin1_general_ci default NULL,
        `HARDWOOD_FLOORS` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_FLOORS` varchar(10) collate latin1_general_ci default NULL,
        `HIGH_CEILINGS` varchar(10) collate latin1_general_ci default NULL,
        `BOOKCASES` varchar(10) collate latin1_general_ci default NULL,
        `COMPOSITE` varchar(10) collate latin1_general_ci default NULL,
        `WOOD` varchar(10) collate latin1_general_ci default NULL,
        `TILE` varchar(10) collate latin1_general_ci default NULL,
        `BUILT_UP_GRAVL` varchar(10) collate latin1_general_ci default NULL,
        `METAL` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_ROOF` varchar(10) collate latin1_general_ci default NULL,
        `ELEC_R_O_BI` varchar(10) collate latin1_general_ci default NULL,
        `GAS_R_O_BI` varchar(10) collate latin1_general_ci default NULL,
        `FREE_STAND_R_O` varchar(10) collate latin1_general_ci default NULL,
        `SELF_CLEAN_OVEN` varchar(10) collate latin1_general_ci default NULL,
        `TWO_OR_MORE_OVENS` varchar(10) collate latin1_general_ci default NULL,
        `INDR_GRILL_VENT` varchar(10) collate latin1_general_ci default NULL,
        `MICROWAVE` varchar(10) collate latin1_general_ci default NULL,
        `DISHWASHER` varchar(10) collate latin1_general_ci default NULL,
        `DISPOSAL` varchar(10) collate latin1_general_ci default NULL,
        `TRASH_COMPACTR` varchar(10) collate latin1_general_ci default NULL,
        `VENT_FAN` varchar(10) collate latin1_general_ci default NULL,
        `REFRIGERATOR` varchar(10) collate latin1_general_ci default NULL,
        `WASHER` varchar(10) collate latin1_general_ci default NULL,
        `DRYER` varchar(10) collate latin1_general_ci default NULL,
        `ICE_MAKER` varchar(10) collate latin1_general_ci default NULL,
        `ICE_MKR_CONNECT` varchar(10) collate latin1_general_ci default NULL,
        `AUTO_GAR_OPENER` varchar(10) collate latin1_general_ci default NULL,
        `CENTRAL_VACUUM` varchar(10) collate latin1_general_ci default NULL,
        `INTERCOM` varchar(10) collate latin1_general_ci default NULL,
        `PANTRY` varchar(10) collate latin1_general_ci default NULL,
        `SATELLITE_SYS` varchar(10) collate latin1_general_ci default NULL,
        `SEC_SYSTEM_OWND` varchar(10) collate latin1_general_ci default NULL,
        `SMOKE_DETECTORS` varchar(10) collate latin1_general_ci default NULL,
        `SKYLIGHTS` varchar(10) collate latin1_general_ci default NULL,
        `WTR_SOFTNR_OWND` varchar(10) collate latin1_general_ci default NULL,
        `WET_BAR` varchar(10) collate latin1_general_ci default NULL,
        `RV_OSMOSIS_OWND` varchar(10) collate latin1_general_ci default NULL,
        `SPA_HT_INSIDE` varchar(10) collate latin1_general_ci default NULL,
        `Jacuzzi_Tub` varchar(10) collate latin1_general_ci default NULL,
        `FENCE_BRICK` varchar(10) collate latin1_general_ci default NULL,
        `FENCE_METAL` varchar(10) collate latin1_general_ci default NULL,
        `FENCE_WOOD` varchar(10) collate latin1_general_ci default NULL,
        `FENCE_PARTIAL` varchar(10) collate latin1_general_ci default NULL,
        `FENCE_NONE` varchar(10) collate latin1_general_ci default NULL,
        `OPEN_PATIO` varchar(10) collate latin1_general_ci default NULL,
        `COVERED_PATIO` varchar(10) collate latin1_general_ci default NULL,
        `WOOD_DECK` varchar(10) collate latin1_general_ci default NULL,
        `INGROUND_POOL` varchar(10) collate latin1_general_ci default NULL,
        `ABOVE_GND_POOL` varchar(10) collate latin1_general_ci default NULL,
        `SPA_HOTTUB_OS` varchar(10) collate latin1_general_ci default NULL,
        `MANUAL_SPRNKLR` varchar(10) collate latin1_general_ci default NULL,
        `AUTO_SPRINKLER` varchar(10) collate latin1_general_ci default NULL,
        `GUEST_QUARTERS` varchar(10) collate latin1_general_ci default NULL,
        `STORAGE_SHED` varchar(10) collate latin1_general_ci default NULL,
        `WORKSHOP` varchar(10) collate latin1_general_ci default NULL,
        `BARN_STABLE` varchar(10) collate latin1_general_ci default NULL,
        `STORM_CELLAR` varchar(10) collate latin1_general_ci default NULL,
        `DOCK` varchar(10) collate latin1_general_ci default NULL,
        `TENIS_CTS` varchar(10) collate latin1_general_ci default NULL,
        `MORE_THAN_ONE_TENIS` varchar(10) collate latin1_general_ci default NULL,
        `GAS_WHAT` varchar(10) collate latin1_general_ci default NULL,
        `ELECTRIC_WHAT` varchar(10) collate latin1_general_ci default NULL,
        `SOLAR_WHAT` varchar(10) collate latin1_general_ci default NULL,
        `WIND_TURBINE` varchar(10) collate latin1_general_ci default NULL,
        `PWR_ATTIC_VENT` varchar(10) collate latin1_general_ci default NULL,
        `TIMED_THERMOST` varchar(10) collate latin1_general_ci default NULL,
        `CEILING_FAN` varchar(10) collate latin1_general_ci default NULL,
        `STORM_WINDOWS` varchar(10) collate latin1_general_ci default NULL,
        `STORM_DOOR` varchar(10) collate latin1_general_ci default NULL,
        `THERMOPANE_WDWS` varchar(10) collate latin1_general_ci default NULL,
        `LANDSCAPED` varchar(10) collate latin1_general_ci default NULL,
        `CUL_DE_SAC` varchar(10) collate latin1_general_ci default NULL,
        `GOLF_COURSE` varchar(10) collate latin1_general_ci default NULL,
        `ALLEY_ACCESS` varchar(10) collate latin1_general_ci default NULL,
        `CORNER` varchar(10) collate latin1_general_ci default NULL,
        `COUNTRY_ACREAGE` varchar(10) collate latin1_general_ci default NULL,
        `CITY_ACREAGE` varchar(10) collate latin1_general_ci default NULL,
        `CITY_LOT` varchar(10) collate latin1_general_ci default NULL,
        `PAVED` varchar(10) collate latin1_general_ci default NULL,
        `CURBS_GUTTERS` varchar(10) collate latin1_general_ci default NULL,
        `COUNTY_ROAD` varchar(10) collate latin1_general_ci default NULL,
        `UNIMPROVED` varchar(10) collate latin1_general_ci default NULL,
        `CALICHE` varchar(10) collate latin1_general_ci default NULL,
        `PAVED_ALLEY` varchar(10) collate latin1_general_ci default NULL,
        `CITY_WATER` varchar(10) collate latin1_general_ci default NULL,
        `CITY_SEWER` varchar(10) collate latin1_general_ci default NULL,
        `WATER_WELL` varchar(10) collate latin1_general_ci default NULL,
        `RURAL_SYSTEM` varchar(10) collate latin1_general_ci default NULL,
        `SEPTIC_TANK` varchar(10) collate latin1_general_ci default NULL,
        `OWNER_AGENT` varchar(10) collate latin1_general_ci default NULL,
        `FLOOD_INSUR_MAY` varchar(10) collate latin1_general_ci default NULL,
        `MEETS_WHEEL_CHR` varchar(10) collate latin1_general_ci default NULL,
        `RESERVED_ITEMS` varchar(10) collate latin1_general_ci default NULL,
        `HOME_WARRANTY` varchar(10) collate latin1_general_ci default NULL,
        `LATENT_DEFECTS` varchar(10) collate latin1_general_ci default NULL,
        `IMMEDIATE` varchar(10) collate latin1_general_ci default NULL,
        `AT_CLOSING` varchar(10) collate latin1_general_ci default NULL,
        `SUBJT_TEN_RGHTS` varchar(10) collate latin1_general_ci default NULL,
        `NEGOTIABLE` varchar(10) collate latin1_general_ci default NULL,
        `ATTACHED` varchar(10) collate latin1_general_ci default NULL,
        `DETACHED` varchar(10) collate latin1_general_ci default NULL,
        `REAR_SIDE_ENTRY` varchar(10) collate latin1_general_ci default NULL,
        `CONVERTED` varchar(10) collate latin1_general_ci default NULL,
        `GOLF_CART` varchar(10) collate latin1_general_ci default NULL,
        `PARKING_PAD` varchar(10) collate latin1_general_ci default NULL,
        `CIRCLE_DRIVE` varchar(10) collate latin1_general_ci default NULL,
        `ASSIGND_PARKING` varchar(10) collate latin1_general_ci default NULL,
        `SEPARATE_FORMAL` varchar(10) collate latin1_general_ci default NULL,
        `FORMAL_AREA` varchar(10) collate latin1_general_ci default NULL,
        `BREAKFAST` varchar(10) collate latin1_general_ci default NULL,
        `BREAKFAST_BAR` varchar(10) collate latin1_general_ci default NULL,
        `WOODBURNING` varchar(10) collate latin1_general_ci default NULL,
        `GAS_LOGS` varchar(10) collate latin1_general_ci default NULL,
        `FREE_STANDING` varchar(10) collate latin1_general_ci default NULL,
        `FIREPLC_SCREEN` varchar(10) collate latin1_general_ci default NULL,
        `DOUBLE_SIDED` varchar(10) collate latin1_general_ci default NULL,
        `DEN_TWO` varchar(10) collate latin1_general_ci default NULL,
        `LIVING_ROOM` varchar(10) collate latin1_general_ci default NULL,
        `BEDROOM` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_ROOM_TWO` varchar(10) collate latin1_general_ci default NULL,
        `ECTOR_CO_ISD` varchar(10) collate latin1_general_ci default NULL,
        `GREENWOOD_ISD` varchar(10) collate latin1_general_ci default NULL,
        `MIDLAND_ISD` varchar(10) collate latin1_general_ci default NULL,
        `OTHER_ISD` varchar(10) collate latin1_general_ci default NULL,
        `BIG_SPRING_ISD` varchar(10) collate latin1_general_ci default NULL,
        `COAHOMA_ISD` varchar(10) collate latin1_general_ci default NULL,
        `FORSAN_ISD` varchar(10) collate latin1_general_ci default NULL,
        `DO_NOT_WANT_AD` varchar(10) collate latin1_general_ci default NULL,
        `AREA` varchar(30) collate latin1_general_ci default NULL,
        `SALE_RENT` char(1) collate latin1_general_ci default NULL,
        `ADDRESS_STREET` varchar(30) collate latin1_general_ci default NULL,
        `ADDRESS_STREET_TWO` varchar(25) collate latin1_general_ci default NULL,
        `ADDRESS_NUMBER` varchar(30) collate latin1_general_ci default NULL,
        `ADDRESS_DIRECTION` varchar(30) collate latin1_general_ci default NULL,
        `Virtual_Tour` varchar(255) collate latin1_general_ci default NULL,
        `Time` bigint(35) default NULL
      )
    Up until now I have been using a single join combining the listings table and the organizations table. Now it is requested that I add the website value into the results - which is only available in the Agents table.

  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)
    OMG, you poor soul, that database was built by an amateur

    you will have no end of trouble getting data out of it efficiently

    oh well

    you were right, the only way to join the listing to the organization is via the name of the organization in LISTING_OFFICE

    however, going from there to the agent is problematic

    it looks like each organization can have multiple agents, and it certainly appears that each agent can have her own website

    so when you say "Now it is requested that I add the website" you face the problem of deciding whether to show all the websites for all the agents for the listing's organization, or how to pick just one website, and which one to pick

    i would go back to your client and get clarification on the requirement in light of this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    OMG, you poor soul, that database was built by an amateur
    My thoughts exactly...

    Quote Originally Posted by r937
    so when you say "Now it is requested that I add the website" you face the problem of deciding whether to show all the websites for all the agents for the listing's organization, or how to pick just one website, and which one to pick
    Each listing belongs to one agent. Each agent belongs to one organization. Each agent can have one website. Thus each listings should only have one website, the website of the listing agent. With the query posted a couple post up I am getting correct results - but on my local machine it takes over 16 seconds to complete the query. On the production server one query pegs out the CPU for way too long.

    There are no indexes or keys defined in the current structure. Might those help at all?

    Woah!!! I just made all of the columns that are being used for the joins indexs - and my query now runs in .0054 seconds! That should do.

  13. #13
    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)
    good thinking! indexes would have been my next suggestion

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

  14. #14
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Next issue.

    The JOIN:

    Code:
    INNER JOIN Agents_Table ON ( Listings_Table.AGENT = CONCAT( Agents_Table.Last_Name, ', ', Agents_Table.first_Name ) )
    Is problematic because the Agent Name is not a unique identifier, so I am getting flawed results again when there is more than one Agent in the Agents Table with the same name.

    So I need to add somehow the restriction that the Agent_Table.Org_ID matches the Organizations_Table.Org_ID from the same row where the Organizations_Table.Org_Name matches the Listings_Table.LISTING_OFFICE.

    Any ideas? I'm really lost on this one.

  15. #15
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright... I think I got it working. Here is the query I have. Suggestions for improvement are welcome:

    Code:
    SELECT Listings_Table. * , 
     Organizations_Table.Phone_Area, 
     Organizations_Table.Phone_Number, 
     Agents_Table.Website
     FROM Listings_Table
     INNER 
     JOIN Organizations_Table, Agents_Table ON Listings_Table.LISTING_OFFICE = Organizations_Table.Org_Name
     AND (Listings_Table.AGENT = CONCAT( Agents_Table.Last_Name,  ', ', Agents_Table.first_Name ) 
     )
     AND Agents_Table.Org_ID = Organizations_Table.Org_ID

  16. #16
    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 don't need us, do you

    minor correction to your query structure (use JOIN syntax exclusively, don't use the table list style) --
    Code:
    select Listings_Table.* 
         , Organizations_Table.Phone_Area
         , Organizations_Table.Phone_Number
         , Agents_Table.Website
      from Listings_Table
    inner 
      join Organizations_Table
        on Listings_Table.LISTING_OFFICE 
         = Organizations_Table.Org_Name
    inner
      join Agents_Table 
        on Listings_Table.AGENT
         = concat(Agents_Table.Last_Name
            ,', ',Agents_Table.first_Name ) 
       and Organizations_Table.Org_ID
         = Agents_Table.Org_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you don't need us, do you
    Whatever. I really appreciate your help. Thanks!!! Thanks for the syntax correction also.


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
  •