query to get record based on joined table column array

Question

i have a query that its result is array like $a=[1, 3, 5]

i need another query which return records from table1 that all b values are in $a=[1,3, 5] so result for this sample is table1.id=1, 2

can i implement this with a query or i have to use php code like array_diff() to check difference between b column and $a?

**table1**
id   
-----------
1     ...
2     ...
3     ...

**table 2**
table1_id    b
------------
1            1
1            3
2            1
3            1
3            4  
4            1
4            3
4            5
4            4

Your question is not clear .. Try explain better .. show a real sampe of data an the real expected result — ScaisEdge
– ScaisEdge, Commented Nov 3, 2016 at 20:59
You can use implode() in PHP and an IN() condition im MySQL. You can also use a JOIN. — Paul Spiegel
– Paul Spiegel, Commented Nov 3, 2016 at 21:32
thank u Paul but my question was can i solve that with single sql query or i need php functions like implode() or array_diff() and .. too? — user006779
– user006779, Commented Nov 3, 2016 at 21:36
Show the two queries, that need to be combined. Ricardos answer goes in the right direction. — Paul Spiegel
– Paul Spiegel, Commented Nov 3, 2016 at 21:42

Ricardo Ortega Magaña · Accepted Answer · 2016-11-03 22:21:59Z

1

If i understand it correctly:

SELECT * FROM table2 WHERE table2.b IN (SELECT id FROM table1 WHERE YourCondition)

If you want to join multiple tables, with matching id for example, use:

Select * from table1 inner join table2 on table1.id=table2.table1_id

edited Nov 3, 2016 at 22:21

answered Nov 3, 2016 at 21:17

Ricardo Ortega Magaña

2,44219 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user006779 Over a year ago

thank u Ricardo, but condition is on table2 and how am i suppose to compare b values per record with $a?

Ricardo Ortega Magaña Over a year ago

The YourCondition, is whatever ID from table1 you need, if you want every record from the table 1, just put "where 1"

user006779 Over a year ago

sorry ricardo. i think i explain my question unclear. i think my answer is something like this from this post:stackoverflow.com/questions/26204792/… select table1.id from table1 join table2 on table1.id = table2.table1.id group by table1.id having sum(case when b not in $a then 1 else 0 end) = 0

Collectives™ on Stack Overflow

query to get record based on joined table column array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related