【Question】

I have two tables over which I want to perform a SQL query. Table A has two fields – Id,Name. Table B has three fields – Id,URL,CaseId, where CaseId points to table A’s Id field. The relationship between table A and table B is one-to-many.

Table A

1    Amy 

2    Kay 

3    Paul 

Table B

1    1.png    1 

2    2.png    1 

3    3.png    2 

I want to get table A’s Id and Name and table B’s URL according to the association. Records in the result set should be unique. Is there anyone who can help me with this? Thanks in advance!

1    Amy    1.png 

2    Kay    3.png 

3    Paul    NULL

【Answer】

To do it in MySQL, we can first join the two tables and then group the joining result set by a.id and get the first record from each group; or we can first group table B by b.caseid, get the first record from each group and then join the result set with table A. The MySQL query can be written like this:

select a.id,a.name,b.url from a left join b on a.id=b.caseid group by a.id

A MySQL group by clause returns the first records in all groups.

But the above query is unscalable though databases supporting window functions can number members in each group and then get the desired one by its number. If you want a universal way of doing this, try SPL (Structured Process Language). Below is the SPL script written in esProc:

Join table A and table B and then group()@1 directly gets the first member from each group. esProc offers JDBC interface to easily embed the SPL script into a Java application.