Join Help

[BebeBlueEyez]

$sql = "SELECT DISTINCT
            tb1.col1, tb1.col2, tb1.col3, tbl2.cola,tbl2.colb
            FROM tb1,tb2
            INNER JOIN tb3 ON tb1.col1 - tb3.colx
             WHERE tb1.col1 = $ID AND tbl2.cola = $ID AND tb3.colz = $other";

That's what i have. Is not working. This is what it used to be:

$sql = "SELECT DISTINCT
            tb1.col1, tb1.col2, tb1.col3
             FROM tb1
              INNER JOIN tb3 ON tb1.col1 - tb3.colx
             WHERE tb1.col1 = $ID AND tb3.colz = $other";

As you can see i've added another tab le.

This table I have added (tb2) holds uploaded document data in chunks. So the ID that is the same ID as tb1, will be repeated in multiple rows.

Therefore when fetching it, the results will show multiple rows with the ID in tb2, but only one row of data in tb3 and tb1. I think this may be my problem, and I've looked in the MYSQL documentation but just am not getting it.

Can someone help explain it to me please?

[nemonoman]

Let me rewrite this in the old-fashioned way I understand:

SELECT DISTINCT
tb1.col1, tb1.col2, tb1.col3, tbl2.cola,tbl2.colb
FROM tb1,tb2 , tb3
WHERE tb1.col1=tb2.cola
AND tb1.col1=tb3.colx

and tb1.col1=$id
and tb3.colz=$other

If you are saying that tb2 has multiple rows where cola=tb1.col1, each row where
concat(tb1.col1, tb1.col2, tb1.col3, tbl2.cola,tbl2.colb)
would show a unique ('DISTINCT') value wil be returned.

same if tb3 has multiple rows where colx=tb1.col1

If multiple rows as above exist in BOTH tb1 and tb2, then you'll get them as cartesian products: you'll get a number of rows =
num of distinct tb1.col1 values num of tb2 rows where cola=tb1.col1 num of tb3 rows where colx=tb1.col1

this will look like

tb1.col1, tb1.col2, tb1.col3, tbl2.cola,tbl2.colb

1,1,1,1,1
1,2,1,1,1
1,2,2,1,1
1,1,2,1,1
1,1,1,2,1
1,2,1,2,1
1,1,2,2,1

etc