• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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');
0
krugar77
Asked:
krugar77
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now