Need help with a SQL query

[cyberia]

I have two tables: a header table H and a description table D (with multiple records for each header record). I need to pick all header records and get a description for each of them from the description table.

The complication is that I have to pick the first description from the description table for each header record.

If I do SELECT * FROM H INNER JOIN D ON H.TRANS_ID = D.TRANS_ID, I naturally get multiple results returned for each header record (each with a different description).

Is there a way to achieve what I need to do in one SQL statement without using cursors or stored procedures?

Thanks.

 

[Kilrsat]

Exact syntax depends on which database software you're running, but most have something similar to this.

Examples -
MSSQL:
SELECT TOP 1 * FROM H INNER JOIN D ON H.TRANS_ID = D.TRANS_ID

MySQL
SELECT * FROM H INNER JOIN D ON H.TRANS_ID = D.TRANS_ID LIMIT 0,1

 

[cyberia]

Thanks Kilrsat, but that won't work. It will return exactly one record, period. I need all header records and their corresponding descriptions.

In other words, if I have 10 records in the H table, and 100 records (10 for each TRANS_ID) in the D table, my original query will return 100 results. Your query will return 1 result. I need a query that returns 10 results.

 

[KLin]

SELECT * FROM H LEFT JOIN D ON H.TRANS_ID = D.TRANS_ID

INNER JOIN = one to one join
LEFT JOIN - many to one
RIGHT JOIN = one to many

 

[MrChad]

Originally posted by: KLin
SELECT * FROM H LEFT JOIN D ON H.TRANS_ID = D.TRANS_ID

INNER JOIN = one to one join
LEFT JOIN - many to one
RIGHT JOIN = one to many

A left join will just ensure that all the values from H get returned, regardless of whether a corresponding D value is found.

 

[b4u]

Let's say, for example, you have the following tables:

tblH:
pk_h | h_code
------------------------------
1 | H0001
2 | H0002
3 | H0003

tblD:
fk_h | pk_d | d_desc
------------------------------
1 | 1 | H1_Desc_1
1 | 2 | H1_Desc_2
1 | 3 | H1_Desc_3
2 | 1 | H2_Desc_1
2 | 2 | H2_Desc_2

Note that I didn't give H3 any description on purpose.

(format in this forums it's a pain, hope you can see where fields start/end)



So you want the output to be:

pk_h | h_code | first_desc
------------------------------
1 | H0001 | H1_Desc_1
2 | H0002 | H2_Desc_1
3 | H0003 | NULL

H3 doesn't have a description, so it returns null.

Right?

If that's what you want, you can use the following statement:

select H.pk_h,
H.h_code,
(select top 1
D.d_desc
from tblD D
where D.fk_h = H.pk_h
order by D.pk_d asc) first_desc
from tblH H
order by h.h_code asc


Some explanation: the "field" that will receive the first description, is named "first_desc", and is composed of a single isolated select, that will return the descriptions from tblD. That same isolated select has a "where" condition that helps recover only the descriptions of the header being processed (linking a field of that select with a field of the parent select), and it has an "order by" that helps you set the order of the descriptions themselfes, then the "top 1" returns just the first record. Done!

You could try the following variant: on the "sub-select", instead of ordering with "asc", try changing it to "desc". That will give the LAST descriptions. That way you can clearly see how it works.


Now this will give you the results you asked for ... now about performance will suffer a penalty with lot's of records ... still it will work.


Did I hit the jackpot?


<edit>added the explanation and some other format improvements</edit>

 

[MrChad]

You could do something like

SELECT H.TRANS_ID, MIN(D.DESCRIPTION) FROM H INNER JOIN D ON H.TRANS_ID = D.TRANS_ID GROUP BY TRANS_ID

The problem is that your requirement for the "first description" is ambiguous. How do you define the order of the descriptions?

 

[KLin]

Originally posted by: MrChad

Originally posted by: KLin
SELECT * FROM H LEFT JOIN D ON H.TRANS_ID = D.TRANS_ID

INNER JOIN = one to one join
LEFT JOIN - many to one
RIGHT JOIN = one to many

A left join will just ensure that all the values from H get returned, regardless of whether a corresponding D value is found.

yea you're right. I didn't really read his question thorougly. plus it's still early in the morning

 

[cyberia]

Originally posted by: b4u
Some explanation: the "field" that will receive the first description, is named "first_desc", and is composed of a single isolated select, that will return the descriptions from tblD. That same isolated select has a "where" condition that helps recover only the descriptions of the header being processed (linking a field of that select with a field of the parent select), and it has an "order by" that helps you set the order of the descriptions themselfes, then the "top 1" returns just the first record. Done!

...


Did I hit the jackpot?

WOW! That's ingenious. Before I saw your reply, I experimented with self-joins, but your approach makes so much sense. Thanks a lot. Perfect 10!

But, you were absolutely right about the performance penalty. Neither your solution nor my self-joins will ultimately fly. I actually have an intermediate table between the two tables I had mentioned. I have too many records in one of the tables. When I apply your approach to all three tables, the query takes too long. (But does work perfectly with two tables.)

So, I guess I will have to create a stored procedure to do the task. Hopefully it will give me a better performance.

But thanks a ton. I am so excited that I just learned such a great SQL feature! :thumbsup:

 

[cyberia]

Originally posted by: MrChad
You could do something like

SELECT H.TRANS_ID, MIN(D.DESCRIPTION) FROM H INNER JOIN D ON H.TRANS_ID = D.TRANS_ID GROUP BY TRANS_ID

The problem is that your requirement for the "first description" is ambiguous. How do you define the order of the descriptions?

I am so glad I am not the only one who had problems with the "first description" concept! This is totally weird as it assumes that the data is ordered physically, and can be absolutely correctly classified as ambiguous in RDBS/SQL world.

That's how the person whose app I am rewriting explained the logic to me. His system has roots in LINC and apparently he has no concept of logical data storage as opposed to physical.

In any case, that's a great solution too. Unfortunately, it'll give me the "first" alphabetical description, and not necessarily the "first" description you might get by other means (whatever means were employed previously). In any case, it is definitely worth further pondering. Thank you!
:thumbsup: