Left join data into 0, 1, or 2 columns depending on flag

[krausr79]

Suppose I have data in my main table:


A
B
C
D

And data in a second table that should attach to the main data in one of two ways (X or Y):
A 1 X
A 2 Y
B 3 X
C 4 Y
The secondary table records will be unique per main data and X/Y.

Then I'd like to make a query that shows all main table data with attached data:
A 1 2
B 3
C 4
D

I feel like this should be easy but it's giving me trouble. I'm currently left joining in two successive queries, but all the conditions I've tried have left things out. I can't find the correct google language for this particular problem.

[CJ_London]

try something like

SELECT m.fld1, x.fld2, y.fld2
FROM (main as m left join secondary as x ON m.fld1=x.fld1) left join secondary as y on m.fld1=y.fld1

[krausr79]

That one gives me two lines for things with both kinds of data. I'm having success with two extra queries on the secondary data: One to get X data and one to get Y data. Then I can left join both of them to the main data with no restrictions.

[CJ_London]

OK then the code would be

SELECT m.fld1, x.fld2, y.fld2
FROM (main as m left join secondary as x ON m.fld1=x.fld1) left join secondary as y on m.fld1=y.fld1
WHERE x.fld3="X" and y.fld3="Y"