英文:
find data that couldn't exist working with 2 tables
问题
SELECT i.IDim, i.title, i.descr, o.status
FROM IMAGES AS i
LEFT JOIN OPERATION AS o ON i.IDim = o.IDim
英文:
3 tables:
IMAGES [IDim, title, descr, etc.]
USERS [IDus, name, surname, etc.]
OPERATION [ID, IDim, IDus, status, reserveDate, etc.]
I need a query that gets all rows of IMAGES and adds the status read from the OPERATION table.
The status field gets value 'READY' or 'LOST' only sometimes (and in that moment a link between the IMAGES and the OPERATION tables is created).
I tried this incomplete and wrong query:
SELECT i.IDim, i.title, i.descr, o.status
FROM IMAGES AS i, OPERATION AS o
WHERE i.IDim = o.IDim
This fetches only the ones in the OPERATION table, but It's possible that some images are not inserted there.
A possibility is to create a reference in OPERATION every time a new item is inserted in IMAGES, setting the status field as 'NONE', for example, but I'd like to avoid it.
How do I write this?
答案1
得分: 1
需要使用左连接来实现这一点。
连接操作已经存在了30年，你应该始终使用它们。
SELECT i.IDim, i.title, i.descr, o.status
FROM IMAGES AS i
LEFT JOIN OPERATION AS o
ON i.IDim = o.IDim
英文:
You need a LEFT JOIN for that.
JOINs are around for 30 years now, you should really start using them all the time
SELECT i.IDim, i.title, i.descr, o.status
FROM IMAGES AS i
LEFT JOIN OPERATION AS o
ON i.IDim = o.IDim
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论