| Author |
 Topic  |
|
|
Rohwer3
Starting Member
12 Posts |
 Posted - 06/21/2012 : 15:37:48
|
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! |
|
|
tkizer
Almighty SQL Goddess
USA
35020 Posts |
|
|
Rohwer3
Starting Member
12 Posts |
Posted - 06/21/2012 : 15:56:11
|
Thanks for the quick response.
That is just showing a 1 to 1 exact match between the two tables.
I need it to be a bit more flexible.
TMP_VIN_STOCK table will only have one stock number (ie R11010)
but newvehicles table can have multiple (ie R11016, R11010)
I need it to search though the newvehicles table and see if that stock number is anywhere in that table. |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3859 Posts |
|
|
Rohwer3
Starting Member
12 Posts |
Posted - 06/22/2012 : 11:24:50
|
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.
|
Edited by - Rohwer3 on 06/22/2012 11:27:20 |
|
|
|
Rohwer3
Starting Member
12 Posts |
Posted - 06/22/2012 : 12:07:58
|
Got it!!!
For those of you following :
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35020 Posts |
|
| |
Topic |
|
|
|
Select from one table not in another with multiple
