Join table if null

Databases
1

Hi I have 2 tables joining using left join:

SELECT e.*, re.* FROM event e
LEFT OUTER JOIN event_recur re ON(e.eventID = re.eventID)

Since both table consist of the same column name e.eventID = re.eventID, if event_recur doesn’t have any record, my main e.eventID will be “overwritten” as NULL. For example:


+----------+--------------+----------+
| eventID  |   title      |  eventID |  <--- this is from event_recur
+----------+--------------+----------+
|   112    |   testing    |   NULL   |
+----------+--------------+----------+

Is there any better way to prevent this instead of manually typing out all the individual columns?

Thanks in advanced for any advise.

2

maybe switch the e.* and re.* around?
still, though laziness is a virtue, I think explicitly naming your columns is the best way.

3

no, it won’t

your result set will have two columns called “eventID” and it is your application language that cannot distinguish between them

the best way around this is to assign column aliases, e.g.

SELECT e.name AS e_name
     , re.name AS re_name
     , ...
4

Yes Rudy in my language can’t distinguish them. Can’t use alias as my code has already been populated with “eventID” name everywhere.

I think I shall go with naming the columns out individually in the sql then. Thank jurn and Rudy.

5

that would be a rename, which is not what i meant

assign column aliases there :slight_smile: