CODEWITHSUNDEEP
sqlms-accessjoin
Markus
Markus

Reputation: 693

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

Upvotes: 0

Views: 60

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Change your WHERE clause to:

WHERE tbl_a_a.KBID < tbl_a_b.KBID;

Upvotes: 0

Ritesh Patel
Ritesh Patel

Reputation: 315

Add one condition

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;

Upvotes: 1

sagi
sagi

Reputation: 40481

If it doesn't matter which one you want :

SELECT t.VBID,MAX(t.KBID),MAX(t.SEQ)
FROM tbl_a t
JOIN tbl_b s
 ON(t.vbid = s.vbid)
GROUP BY t.vbid

Upvotes: 1

Related Questions