How do I compare two fields from two tables where one field is like another but not exact?

Im stumped and so are the people helping me so Ill try to ask this a different way.

I have two tables. I need to write a query to get all of the data based on where part of one field from one table is contained in the full field of the other table.

It is a classic needle in a haystack. Except all of my haystacks are not standard.

In literal terms, table_a.small_id is part of table_b.big_id.

I know the following statement wont work because it is trying to find the literal string (the actual text) table_a.small_id as opposed to the value of the variable. But if it could treat it as a variable, thats what I need to do.

SELECT table_a.x, table_a.small_id, table_b.big_id
FROM table_b, table_a
WHERE table_b.big_id LIKE '%table_a.small_id%'

Ive created a view that pulls out all of the distinct small_id possibilities. Essentially, I need to compare every big_id against the 200 possible small_ids and return the combined data that matches for each record. How the heck do you do this in standard SQL? Im using 5.0 if that helps.

Below is the sql to create the simple test databases.
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for table_a
-- ----------------------------
CREATE TABLE `table_a` (
  `id` int(11) NOT NULL auto_increment,
  `time` int(11) default NULL,
  `x` varchar(255) default NULL,
  `y` varchar(255) default NULL,
  `small_id` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `table_a` VALUES ('1', '60521', '4', '12', 'i014');
INSERT INTO `table_a` VALUES ('2', '60521', '8', '32', 'i014');
INSERT INTO `table_a` VALUES ('3', '60522', '3', '74', 'i015');
INSERT INTO `table_a` VALUES ('4', '60522', '3', '60', 'i016');
INSERT INTO `table_a` VALUES ('5', '60522', '11', '50', 'i016');
INSERT INTO `table_a` VALUES ('6', '60522', '27', '8', 'i016');
INSERT INTO `table_a` VALUES ('7', '60523', '14', '43', 'i014');
 
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for table_b
-- ----------------------------
CREATE TABLE `table_b` (
  `id` int(11) NOT NULL auto_increment,
  `time` int(11) default NULL,
  `a` varchar(255) default NULL,
  `b` varchar(255) default NULL,
  `big_id` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `table_b` VALUES ('1', '60521', '6', '22', '1208_i014_65');
INSERT INTO `table_b` VALUES ('2', '60521', '7', '15', '1208_i014_65');
INSERT INTO `table_b` VALUES ('3', '60522', '11', '19', '390_i015_432');
INSERT INTO `table_b` VALUES ('4', '60522', '8', '23', '90_i016_32');
INSERT INTO `table_b` VALUES ('5', '60523', '31', '50', '523_i014_9');
krugar77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
SELECT table_a.x, table_a.small_id, table_b.big_id
FROM table_b, table_a
WHERE table_b.big_id LIKE CONCAT('%', table_a.small_id ,'%')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
krugar77Author Commented:
Oddly enough it works with my test data but not with my actual data. Hrmmmm....
0
krugar77Author Commented:
I am using a table and a view instead of a two tables in my actual data. Would that matter?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
eventually a data type difference?
like char vs varchar somewhere?
0
krugar77Author Commented:
Ha!... Damn Microsoft Windows... It was putting a ¡ at the end of the last field in every record. So it was trying to do a like "whatever " with a space instead of "whatever" without.

I edited my import script to read:
LINES TERMINATED BY '\r\n'

instead of:
LINES TERMINATED BY '\r'

All is good now and your code works. A million thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.