0

Does my query follow correct format? I can't find any examples of using LIKE and ON, so I am not sure if my query is the problem. Looking for any suggestions or feedback:

SELECT * 
FROM table_1 a
LEFT JOIN table_sql ON a.case_id LIKE '%45305%'
pirho
  • 11,565
  • 12
  • 43
  • 70
OldWest
  • 2,355
  • 7
  • 41
  • 61
  • When joining tables you generally want to compare one or more columns from each. Maybe if you show some sample data and expected query output your intent will be made more clear. – mechanical_meat Feb 26 '12 at 01:15
  • I thought I was doing that? a.case_id LIKE '%45305%' – OldWest Feb 26 '12 at 01:17
  • It looks like whatever you're trying to do, you're going about it the wrong way. What is the output you expect, and what do the tables look like? – menacingly Feb 26 '12 at 01:19
  • Sorry for not adding more info. I want to return all columns from table_1 and I also want to return all columns from table_sql where case_id on table_1 is LIKE (wildcard string) case_id in table_sql. – OldWest Feb 26 '12 at 01:22
  • These tables only have a few fields. And the match should be on the case_id, but with a LIKE instead of the normal = . – OldWest Feb 26 '12 at 01:23
  • Data in table_1 case_id is: 1234, 2345, 7865 etc... and the table_sql case_id data is: 01234000, 92345000, 007865234 etc ... you can see the three would match with a LIKE %7865% would get a match on 007865234. – OldWest Feb 26 '12 at 01:26
  • So I am guessing there is not a way to do a join with a like match? – OldWest Feb 26 '12 at 01:33
  • I don't think it's possible to solve efficiently because the position at which the shorter id will appear in the longer id isn't structured. Since it could appear anywhere in it, it will result in a ghastly query. If you have the capability to change this schema at all, it will save you a lot of heartache. – menacingly Feb 26 '12 at 01:40

4 Answers4

3

This is not correct, you need to specify which column you want to use for the JOIN, but you can do something like this

SELECT * from table_1 AS a
   LEFT JOIN table_sql AS b
   ON a.case_id = b.id
WHERE
   a.case_id LIKE '%45305%'
scibuff
  • 13,377
  • 2
  • 27
  • 30
1

Since you want to use LIKE instead of =, is this what you want?

SELECT * FROM table_1 a
LEFT JOIN table_sql ON 
 a.case_id LIKE CONCAT('%', table_sql.case_id, '%')

See also #4420554

Community
  • 1
  • 1
Emilio Silva
  • 1,942
  • 14
  • 17
  • That's exactly what I am trying to do, but not sure why the CONCAT would solve the query. – OldWest Feb 26 '12 at 01:36
  • Your example is using the static value `%45305%`, so it's not really joining with `table_sql`. My example does the join if `table_sql.case_id` is contained in `a.case_id`. Do you have an example of `case_id`'s that must match? – Emilio Silva Feb 26 '12 at 01:41
  • For example, if there is a line where `table_sql.case_id` is 45305, this line will be joined with all lines from `table_1` where `a.case_id` is `LIKE '%45305%'`. – Emilio Silva Feb 26 '12 at 01:44
1

You can use LIKE in a join, but it sounds like what you you really want is a UNION:

SELECT case_id 
     FROM table_1 a
WHERE a.case_id LIKE '%45305%'
UNION 
SELECT case_id 
     FROM table_sql s
WHERE s.case_id LIKE '%45305%'

If you need to keep track of which table the result came from, you can do something like:

SELECT 'table_a' AS what_table, case_id 
     FROM table_1 a
WHERE a.case_id LIKE '%45305%'
UNION 
SELECT 'table_b', case_id 
     FROM table_sql s
WHERE s.case_id LIKE '%45305%'
Andrew
  • 4,574
  • 26
  • 31
0

Try this, using CONCAT(). it was helpful for me, when I was selecting the full table data:

 SELECT p.post_name,p.post_title,p.post_type,cl.*
FROM ".$wpdb->posts." AS p
LEFT JOIN clicks AS cl
ON cl.podcast_url LIKE CONCAT('%',p.post_name,'%')
WHERE p.post_type = 'team'
montie
  • 494
  • 8
  • 14