SYNTAX for JOIN

[dayankoven]

Hi there fellow experts,

I have two table which look like:-

TABLE A

FST_NAME LAST_NAME
ALBERT PETE
ADRIAN JAKE
MARCUS KING

TABLE B

LAST_NAME ROW_ID
ALBERT 1
ALBERT 2
MARCUS 3

What i need to do is to search in TABLE A for FST_NAME and find a corresponding match in TABLE B under column LAST_NAME. The problem, is that TABLE B might contain duplicate entries for LAST_NAME. Therefore, i only need to select the first record ie in this case would be (ALBERT with ROW_ID = 1). I need to return both LAST_NAME and ROW_ID. Does anyone know how i could achieve the above. Thanks in advance.

 

[rcurva]

You can try a query within a select;

SELECT a.fst_name, b.last_name, b.rwid
FROM tableA a,
(SELECT last_name, MIN(row_id) rwid
FROM tableB
GROUP BY last_name ) b
WHERE a.last_name = b.last_name;

My assumption here is that, you need the record in B table with the minimum value for row_id to determine which last_name must be used.

Alternatively, you can use the MAX() aggregate function if you want it otherwise.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

I think the way is pretty straightforward:

SELECT a.last_name, b.last_name, min(b.row_id)
FROM a, b
WHERE a.last_name = b.last_name
group by a.last_name, b.last_name;