Complex data access from two tables, I'm struggling..

[linkin123]

Please read the whole question, otherwise it won't make anysense.

I have two tables (positive connections and negative connections, as shown below)

each table has 3 columns:
ID1 (auto_increment unique ID)
ID2 (let's call this a picture)
ID3 (let's call this an object in pictures)

for example, a picture with on a beach would contain a sun,sea,sand, and a castle. but the picture can have negative objects under negative_connections table.
instead of words, im using numbers.

picture 3 contain 62,8,42,15 in positive_connections table
picture 3 contain 25,95 abd 33 in negative_connections
picture 4 contain 6 and 59 in positive_connections
picture 4 contain 44 and 75 in negative_connections

as shown here:
---------------------------
Table1: positive_connections
---------------------------
ID ID2 ID3
1 3 62
2 3 8
3 3 42
4 3 15
5 4 6
6 4 59
---------------------------

---------------------------
Table2: negative_connections
---------------------------
ID ID2 ID3
1 3 25
2 3 95
3 3 33
4 4 44
5 4 75
---------------------------

what I need is:
if i input the following numbers (ID3)
input (62,42,15,6,33)
output should be, sorted by most common(ID2):
ID2 Occuriences
3 2 ( it is not 3, because we substracted 1 since an input was found in negative_connections table)
4 1

so basicly from the input, find the most common shared ID3's in positive_connections, sort them by occuriences(I have the code for this:

select id2,count(*) occurencies from positive_connections where id3 in (62,42,15,6,33) group by id2 order by occurencies desc;
-thanks to a tek-tips member

then whatever ID3's from the original input found in negative_connections (in this case 33), then substract the number 1 from each occuriences.


this will obviously involve joining tables, i assume.
i know this is hard but any help is highly appreciated


p.s. thanks in advance and i'm not coding a picture search program, the picture is just an example.
if you have any question, clarification , please please ask

 

[hvass]

Joining tables you can visualise as joining tables side by side you need to join them end to end which you can do by writing records into a new table or with a union.

FWIW

drop temporary table if exists t;
create temporary table t
select
id2,
count(*) as score
from positive
where id3 in (62,42,15,6,33)
group by id2
union
select
id2,
-count(*) as score
from negative
where id3 in (62,42,15,6,33)
group by id2;
select
id2,
sum(score)
from t
group by id2;

- but I cannot think of an instance when you would want to do something like this there may well be a better way of designing your database that would make the query more 'normal/traditional'

 

[Kozusnik]

I agree with the last statement. I'd change the database structure. Maybe add another field pos vs. neg (boolean). Keep it as 1 table with 4 fields.
Mark