Markus Markus - 1 month ago 4
SQL Question

sql join two tables, with non repeating results

I have two tables

tbl_a

VBID | KBID | SEQ
4 | 5 | 1
4 | 6 | 2
5 | 6 | 1
5 | 8 | 2


tbl_b

VBID | more columns to be used
4 | bar
5 | foo


what i got so far is

SELECT
tbl_b.VBID,
tbl_a_a.KBID,
tbl_a_b.KBID
FROM (tbl_b
INNER JOIN tbl_a AS tbl_a_a ON tbl_b.VBID = tbl_a_a.VBID)
INNER JOIN tbl_a AS tbl_a_b ON tbl_b.VBID = tbl_a_b.VBID
WHERE tbl_a_a.KBID <> tbl_a_b.KBID;


the result I'm getting is

VBID | tbl_a_a.KBID | tbl_a_b.KBID
4 | 6 | 5
4 | 5 | 6 <-- do not need this


I don't need that second row (or first) because it represents the same data, what do I have to change in my query to only receive 1 row
the more complicated thing is that I am using ACCESS

Answer

Change your WHERE claue to:

WHERE tbl_a_a.KBID < tbl_a_b.KBID;
Comments