Joins

gadget2020

I can't seem to get my head round the joins that would be required for this;

Table A: ID1,ID2
Table B: ID,feilds

Given an outside IDx, I want to
SELECT feilds
FROM TableB
WHERE B.ID=DISTINCT(IDx AND
(SELECT A.ID1 WHERE A.ID2=IDx) AND
(SELECT A.ID2 WHERE A.ID1=IDx))

Can anyone translate this into (working)MYSQL before my brain starts dribbling out my ears? 🙁

Roger_Ramjet

What a mess. Don't foloow what you are after at all.

What are the IDs in A and B?
Are the IDs in A a matrix of IDs in B?

Be more explicit about the contents and their relationships, we are not mind readers.

gadget2020

Table B is a list of (eg) films with information about that film.
Table A (should) enable me to build a relationship between various films;

Table B contains 4 records;
ID[01]=Terminator
ID[02]=T2
ID[03]=Rise of the Machines
ID[04]=Children Of The Corn

Table A says "hey, you're interested in Terminator [01]; have a look at T2 [02] and [03] Judgement day, and Children of the Corn[04] had Jodie Foster in it"
ID1 ID2
01 02
01 03
01 04
02 03
02 04

The ID's should work so that it dosn't matter if it's 01,02 or 02,01.
So with the above info, I want a query that when on record...
[01] will list records [01][02][03][04]
[02] will list records [01][02][03][04]
[03] will list records [01][02][03]
[04] will list records [01][02]

Any ideas? The first post was the best I could come up with... I think I may have to "join" table A to it's self ?

edit
Would this work?...

SELECT * FROM item WHERE
ItemID=".$LinkID." OR
ItemID=DISTINCT(
(link.ItemID1=".$LinkID." AND
link.ItemID2=ItemID)
OR
(link.ItemID2=".$LinkID." AND
link.ItemID1=ItemID)
)

gadget2020

The above tells me it can't find the "link" table...

Perhaps this?

SELECT * FROM item I
JOIN link L
ON (I.".$LinkID."=L.ItemID||L.ItemID)

Or this?

SELECT * FROM items WHERE ItemID IN (
(SELECT ItemID1 FROM Link WHERE ID2=$LinkID
UNION
SELECT ItemID2 FROM Link WHERE ID1=$LinkID))