Select from one table not in another with multiple

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from one table not in another with multiple

Author

Topic

Rohwer3
Starting Member

12 Posts

Posted - 2012-06-21 : 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

38200 Posts

Posted - 2012-06-21 : 15:47:25

select name,newused,year,make,model,price,color,miles,vin
from TMP_VIN_STOCK t
where not exists (select * from stocknum s where t.name = s.stocknum)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Rohwer3
Starting Member

12 Posts

Posted - 2012-06-21 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-21 : 16:41:54

Please post DDL, DML and expected output so we can help you better. Here are some links that can help you prepare that information in a consumable format:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Rohwer3
Starting Member

12 Posts

Posted - 2012-06-22 : 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.

Rohwer3
Starting Member

12 Posts

Posted - 2012-06-22 : 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

38200 Posts

Posted - 2012-06-22 : 12:58:28

FIND_IN_SET? What rdbms are you using?

See, there's a bit of information you left out about stocknum as your query is equivalent to mine except for that FIND_IN_SET/REPLACE business. That's the type of info we need to know to accurately answer a question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources