Can SQL do conditional join?

[Semidevil]

I have a ''student" table I want to join with an "ID" table.

The "ID" table has 3 columns: "code" (I.e xxx), "sub code" (xxx-yy), and "type" (I.e, "aaa" or "bbb").

My issue is I want to left join by "xxx" when type = "aaa" and join by "xxx-yy" when type = "bbb".

How do I join with this condition?

I tried

select *

From students s
Left join ID as IDnum on s.code = IDnum.code and idnum = 'aaa'
Left join ID as IDnum on s.subcode = IDnum.code and idnum = ''bbb'

This gives me an error for using the table twice in my join

 

[KB]

Doing a join is just like doing a WHERE statement which means you can include functions, ORs ,ANDs etc.

From students s
Left join ID as IDnum on (s.code = IDnum.code and idnum = 'aaa') OR (s.subcode = IDnum.code and idnum = ''bbb')

 

[Semidevil]

Thanks. That makes sense. Now, when I do a group by, it gives me an 'invalid column name," and it is pointing to all my group by lines.

(I realize I need the aggregate functions. These are all in my code)

 

[rifken2]

What's your Query now? Your initial query didn't show a group by

 

[nakedfrog]

I have a ''student" table I want to join with an "ID" table.

The "ID" table has 3 columns: "code" (I.e xxx), "sub code" (xxx-yy), and "type" (I.e, "aaa" or "bbb").

My issue is I want to left join by "xxx" when type = "aaa" and join by "xxx-yy" when type = "bbb".

How do I join with this condition?

I tried

select *

From students s
Left join ID as IDnum on s.code = IDnum.code and idnum = 'aaa'
Left join ID as IDnum on s.subcode = IDnum.code and idnum = ''bbb'

This gives me an error for using the table twice in my join

In addition to what was mentioned previously, if you alias the second reference to the table differently, that would work too.

select *
From students s
Left join ID as IDnum on s.code = IDnum.code and idnum = 'aaa'
Left join ID as IDnum2 on s.subcode = IDnum2.code and idnum = ''bbb'