Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    7

    Unanswered: Select from one table not in another with multiple values

    I am trying to select results from one table that are not in another but a recent change broke my script.

    The stocknum field now has multiple stock numbers (R12155, R12186, R12198) instead of just 1 like before.

    OLD SCRIPT
    --------------------
    select name,newused,year,make,model,price,color,miles,vin from TMP_VIN_STOCK where name NOT IN (SELECT stocknum FROM newvehicles)
    --------------------

    How can i find results in TMP_VIN_STOCK that are not in newvehicles with the multiple stock numbers.

    Like, Locate.... my head hurts!

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.name
         , t.newused
         , t.year
         , t.make
         , t.model
         , t.price
         , t.color
         , t.miles
         , t.vin 
      FROM tmp_vin_stock AS t
    LEFT OUTER
      JOIN newvehicles AS n
        ON FIND_IN_SET(t.name,n.stocknum)
     WHERE n.stocknum IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    7

    Still not working

    Still not getting the right results.....

    below is more info on the structure.

    My 2 sample tables
    ------------------------
    CREATE TABLE `cms_newvehicles1` (
    `newused` mediumtext,
    `year` mediumtext,
    `make` mediumtext,
    `model` mediumtext,
    `price` mediumtext,
    `color` mediumtext,
    `stocknum` mediumtext
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    INSERT INTO `cms_newvehicles1` VALUES('New', '2012', ' Honda ', 'CBR600RRB - CBR600RR', '11990', 'Red', 'R12155, R12186, R12198');
    INSERT INTO `cms_newvehicles1` VALUES('New', '2011', ' Honda ', 'CBR600RRB - CBR600RR', '11199', 'Black', 'R11016, R11010');
    INSERT INTO `cms_newvehicles1` VALUES('New', '2012', ' Kawasaki ', 'KAF950FCF-Mule 4010 Trans4x4', '11699', 'Green', 'K12017');
    INSERT INTO `cms_newvehicles1` VALUES('New', '2012', ' Triumph ', 'STREET TRIPLE R', '9599', 'Red', 'T12021, T12019');
    INSERT INTO `cms_newvehicles1` VALUES('New', '2012', ' Triumph ', 'THRUXTON A1', '9294', 'Red', 'T12004');

    ----------------------------------------------------------
    CREATE TABLE `TMP_VIN_STOCK1` (
    `name` mediumtext,
    `newused` mediumtext,
    `year` mediumtext,
    `make` mediumtext,
    `model` mediumtext,
    `price` mediumtext,
    `color` mediumtext
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    INSERT INTO `TMP_VIN_STOCK1` VALUES('R12155', 'N', '2012', 'HONDA', 'CBR600RR', '11990', 'RED');
    INSERT INTO `TMP_VIN_STOCK1` VALUES('R12186', 'N', '2012', 'HONDA', 'CBR600RR', '11990', 'RED');
    INSERT INTO `TMP_VIN_STOCK1` VALUES('R11016', 'N', '2011', 'HONDA', 'CBR600RRB', '11199', 'BLACK');
    INSERT INTO `TMP_VIN_STOCK1` VALUES('R11010', 'N', '2011', 'HONDA', 'CBR600RRB', '11199', 'BLACK');
    INSERT INTO `TMP_VIN_STOCK1` VALUES('PM0191B1', 'U', '2008', 'HONDA', 'CBR600RR8', '8499', 'SILVER');

    --------------------------------------------------------------
    This is what I tried thus far

    select name,newused,year,make,model,price,color from TMP_VIN_STOCK1 where name NOT IN (SELECT stocknum FROM cms_newvehicles)

    SELECT t.name, t.newused, t.year, t.make, t.model, t.price, t.color
    FROM TMP_VIN_STOCK1 AS t
    LEFT OUTER
    JOIN cms_newvehicles1 AS n
    ON FIND_IN_SET(t.name,n.stocknum)
    WHERE n.stocknum IS NULL

    ---------------------------------------------------------------

    the right results should be only display the unique stocknumbers in the TMP_VIN_STOCK1.name that arent in cms_newvehicles1.stocknum

    The only result that should show is the following :
    PM0191B1, U, 2008, HONDA, CBR600RR8, 8499,SILVER
    ------------------------
    I will be doing the same query backwards as well to tell me which vehicles i can remove also.

    Hope that is what you need.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the test data, it helped pinpoint the problem...
    Code:
    SELECT t.*
      FROM TMP_VIN_STOCK1 AS t
    LEFT OUTER
      JOIN cms_newvehicles1 AS n
        ON FIND_IN_SET(t.name,REPLACE(n.stocknum,' ',''))
     WHERE n.stocknum IS NULL
    p.s. you don't use MEDIUMTEXT in your real tables that way, do you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    7

    medium text

    heck yeah! mediumtext is awesome!!!..... haha no, Just wanted to show you the data really.

    r937, thank you soo much for your assistance with this problem.

    If you were in Nebraska I would buy you a beer!

  6. #6
    Join Date
    Jun 2012
    Posts
    7

    reverse

    How would I run the same query in reverse? Find the vehicles in cms_newvehicles that aren't in TMP_VIN_STOCK ??

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    switch the tables in the left outer join, and test the other table's column for IS NULL

    give it a try, and post your query if it doesn't work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •