Posted by Thomas R. Hummel on 06/20/61 11:52

Erland Sommarskog wrote:
> Without a sequence number in Related_Table it becomes a lot more difficult.
> So I changed Related_Table to:
>
> CREATE TABLE dbo.Related_Table (
> my_id INT NOT NULL,
> seq_no tinyint NOT NULL CHECK (seq_no BETWEEN 1 AND 4),
> my_code CHAR(6) NOT NULL )
>
> and produced this query (with a single LEFT JOIN):
>
> SELECT M.my_id, M.line_number,
> code1 = MIN(CASE R.seq_no WHEN 1 THEN R.my_code END),
> code2 = MIN(CASE R.seq_no WHEN 2 THEN R.my_code END),
> code3 = MIN(CASE R.seq_no WHEN 3 THEN R.my_code END),
> code4 = MIN(CASE R.seq_no WHEN 4 THEN R.my_code END)
> FROM Main_Table M
> LEFT JOIN Related_Table R ON M.my_id = R.my_id
> GROUP BY M.my_id, M.line_number
> ORDER BY M.my_id, M.line_number

Thanks for the interesting solution Erland. In the real life case of
course, I will have many more columns than just my_id and line_number.
I realize that everything is dependent on the specific data, etc., but
in general, would you expect this to perform better than:

SELECT M.my_id,
M.line_number,
R1.my_code AS code1,
R2.my_code AS code2,
R3.my_code AS code3,
R4.my_code AS code4
FROM dbo.Main_Table M
LEFT OUTER JOIN dbo.Related_Table R1 ON R1.my_id = M.my_id AND
R1.seq_no = 1
LEFT OUTER JOIN dbo.Related_Table R2 ON R1.my_id = M.my_id AND
R1.seq_no = 2
LEFT OUTER JOIN dbo.Related_Table R3 ON R1.my_id = M.my_id AND
R1.seq_no = 3
LEFT OUTER JOIN dbo.Related_Table R4 ON R1.my_id = M.my_id AND
R1.seq_no = 4

I'll experiment with both solutions if I can add a sequence number, but
I was just a little surprised to see your query once the seq_no was
added.

Thanks,
-Tom.

[Back to original message]