twan twan - 6 months ago 7
SQL Question

Is it possible to merge these tables into one array value

I'm trying to combine some data from three different tables into a usuable array but I'm stuck on how to approach this.

First let me explain the structure:

My 'main' table

snm_content
:

I only want to use
id
here

enter image description here

Second table 'snm_fieldsandfilters_connections' :

element_id
is important here, it has the same value as
id
in the next table

enter image description here

Third table 'snm_fieldsandfilters_elements':

Here
item_id
is important and
item_id
is the same value as
id
in 'snm_content'

enter image description here

So for example I add another article (the items in snm_content) which has
id
4, then another row will be added in 'snm_fieldsandfilters_connections' with
element_id
22 and there will also be another row added to 'snm_fieldsandfilters_elements' with
id
22 and
item_id
4.

How can I get
element_id
from'snm_fieldsandfilters_connections' to match the
id
in 'snm_fieldsandfilters_elements'? And then compare
id
from 'snm_content' with
item_id
from 'snm_fieldsandfilters_elements'.

I hope I explained it well enough.

What is the best way to achieve this? (I mean SQL wise)

Answer

It sounds like you're asking about combining data from different tables. The way to do this is using SQL JOINs. I suggest you read up on them if you're not aware of how to use them. In the meantime, as an example, this should solve your specific problem, if I've understood the requirements correctly:

select ct.id 
from snm_content ct
inner join snm_fieldsandfilters_elements fe on fe.item_id = ct.id
inner join snm_fieldsandfilters_connections cn on cn.id = fe.element_id