Join with where and contains clause

Question

I am not able to write a sql query where I need to join 2 tables and get the common value of both.

something like this,

select control_id from ProjectImage
where group_id ="20"
Join
(select images from coupon where coupon_name is "test" and images contains control_id of projectImage)

images is a list of contol_id's separated by comma's.

So in the end I want only those control ids which are present in the coupon's table image column.

ProjectImage table ---- 
         image_id   bigint(20)  
    control_id  varchar(255)
    name    varchar(255)
    project_id  bigint(20)      
    group_id    bigint(20)

Coupon table:
    id  bigint(20)  
    image   varchar(1250)   
    name    varchar(255)    
    status  int(11)     
    wafer_id    bigint(20)

select field from table1 join table 2 on where https://dev.mysql.com/doc/refman/5.0/en/join.html — Akhil, Jul 01 '15 at 06:04
first, is it MySql or Sql Server? Second, joins should be before the where clause — Zohar Peled, Jul 01 '15 at 06:04
possible duplicate of [MySQL join with where clause](http://stackoverflow.com/questions/1219909/mysql-join-with-where-clause) — Akhil, Jul 01 '15 at 06:05
So the foreign key in coupon table is in the list of control_ids separated by comma? That is how you want to connect those 2 tables? — Raging Bull, Jul 01 '15 at 06:05

score 0 · Answer 1 · edited Jul 01 '15 at 06:30

0

you can join table on some key eg primary key or you can use something like

select control_id from ProjectImage where group_id ="20" and
control_id contains(select images from coupon where coupon_name is
"test" and images contains control_id)

edited Jul 01 '15 at 06:30

Chiragkumar Thakar

3,616
5
37
49

answered Jul 01 '15 at 06:08

ABC

159
2
16

contains require column and text but here it is not able to resolve the second query – XXDebugger Jul 01 '15 at 06:50

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

if I get your question right, your query should probably look like this

SELECT ProjectImage.control_id, coupon.images
FROM ProjectImage
JOIN coupon ON ProjectImage.control_id = coupon.control_id
WHERE ProjectImage.group_id ="20"
    AND coupon.coupon_name = 'test'

If it does not work that way, please, provide us the table structure for ProjectImage and Coupon.

Edit With the structure provided, you may follow this thread and split the column image into separate rows and then use casual join, for example on a temporary table

edited May 23 '17 at 11:43

Community

1
1

answered Jul 01 '15 at 06:08

Zax

658
5
11

2

there is nothing like coupon.control_id. there is no common column in both tables. the only thing is the control_id of projectImage table is a subset of images in coupon table. – XXDebugger Jul 01 '15 at 06:15
@PSDebugger if there is no common column then you can not use join. – Chiragkumar Thakar Jul 01 '15 at 06:27
the only common things is the control_id is there in the images ( images is a collection of different id's where control_id will be one of them). So i need to compare if the control_id from projectImage table is there in the images column then return that. – XXDebugger Jul 01 '15 at 06:31
@PSDebugger could you give us the table structure? – Zax Jul 01 '15 at 07:36
added in the question- – XXDebugger Jul 01 '15 at 07:44
Referring to Zohar Peleds comment - what database are you using? – Zax Jul 01 '15 at 07:56
Anyway, I can't really think of anything more useful then using a 'LIKE' statement - will add to previous answer in a minute – Zax Jul 01 '15 at 07:59
Tried like. Its returning null. Using mysql db – XXDebugger Jul 01 '15 at 08:21
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/82055/discussion-between-psdebugger-and-zax). – XXDebugger Jul 01 '15 at 08:22

Join with where and contains clause

2 Answers2