MySql update selection if column is null in one request-CodePudding

I have a SQL database with 2 tables and I need to get this data in ONE REQUEST to the server.

I am trying to get the startDate, endDate, and title data from the event table

Then I am also trying to get the source and eventId data from the photo table

So far, i have this which works perfect, but there's one problem.

SELECT event.startDate, event.endDate, event.id, event.title, photo.source, photo.eventId FROM event, photo 
WHERE event.id = 'UNIQUE ID NUMBER' AND photo.eventId = 'UNIQUE ID NUMBER';

Sometimes, the photo.eventId is equal to NULL meaning that nothing will return. If this is the case, the only data I need is data from the first table event which is the three columns startDate, endDate, and title

The return should then have the same output as this:

SELECT event.startDate, event.endDate, event.id, event.title FROM event WHERE event.id = 'UNIQUE ID NUMBER';

CodePudding user response：

What you describe is the definition of a LEFT join of event to photo:

SELECT e.startDate, e.endDate, e.id, e.title, 
       p.source, p.eventId 
FROM event e LEFT JOIN photo p 
ON p.eventId = e.id 
WHERE e.id = 'UNIQUE ID NUMBER';

CodePudding user response：

I found a way to cheat this using by combining the two statements with UNION with the code below and then adding two extra NULL coumns to my second statement. This allows both table's data to be shown

SELECT event.startDate, event.endDate, event.id, event.title, photo.source, photo.eventId FROM event, photo 
WHERE event.id = 'UNIQUE ID NUMBER' AND photo.eventId = 'UNIQUE ID NUMBER' 
UNION SELECT event.startDate, event.endDate, event.id, event.title, null, null FROM event WHERE event.id = 'UNIQUE ID NUMBER';

CodePudding user response：

If we use a left join the results from the table event will be returned, even if there are no matching records in photo. NB I have modified the WHERE so as to show the results of both cases: matching and non-matching.
If there are more than one line in photo for an event we will get one row per photo. In that case you should look at using STRING_AGG() for the fields from photo and GROUP BY the fields from event.

create TABLE event(
startDate date,
endDate date,
id int, 
title varchar(25)
  );
create table photo(
  source varchar(25),
  eventID int
  );
insert into event (id, title) values (1, 'event one'),(2,'event two');
insert into photo values('source 1',1);

SELECT 
  event.startDate, 
  event.endDate, 
  event.id, 
  event.title, 
  photo.source, 
  photo.eventId 
FROM event
LEFT JOIN photo 
ON event.id = photo.eventId 
WHERE event.id = 1 or event.id = 2;

startDate | endDate | id | title     | source   | eventId
:-------- | :------ | -: | :-------- | :------- | ------:
null      | null    |  1 | event one | source 1 |       1
null      | null    |  2 | event two | null     |    null

SELECT event.startDate, event.endDate, event.id, event.title, photo.source, photo.eventId FROM event, photo 
WHERE (event.id = 1 AND photo.eventId = 1)
or (event.id = 2 AND photo.eventId = 2);

startDate | endDate | id | title     | source   | eventId
:-------- | :------ | -: | :-------- | :------- | ------:
null      | null    |  1 | event one | source 1 |       1

db<>fiddle here