简单的MySQL Join替换外键 [英] Simple MySQL Join to replace foreign key

问题描述

我有2张桌子，门票和州.票证具有与state.id对应的外键状态

I have 2 tables, tickets and state. Tickets has a foreign key state which corresponds to state.id

SELECT tickets.id AS 'Ticket Id', 
       state.description AS 'State'
FROM tickets 
LEFT JOIN state ON tickets.state = state.id
WHERE tickets.id = 1;

因此，如果在票证中有一行，tickets.id = 1和tickets.state = 1，而在状态行中有state.id = 1和state.description ='Open'

So if I have a row in tickets with tickets.id = 1 and tickets.state = 1 and I have a row in state with state.id = 1 and state.description = 'Open'

如何获取结果票证ID = 1，州=打开?

How do I get a result Ticket Id = 1, State = Open?

这确实可行，由于某种原因，状态表为空...

This does work, for some reason the state table was empty...

推荐答案

对我来说，这似乎是从您编写的查询中得到的...?

To me it looks like that is what you would get from the query you've written...?

如果要从数据库中获得准确的结果"Ticked Id = 1，State = Open"，请查看concat()函数.如果您想将状态直接添加到票证表中，我将首先添加一个新列，然后使用更新查询对其进行更新，然后删除旧的tickets.state列.或者，也许您真正需要的是使用上述查询定义的视图.

If you want to have the exact result "Ticked Id =1, State = Open" from your db, look into the concat()-function. If you would like to have the states directly into your ticket-table, I would first add a new column, then update that using an update query, then drop the old tickets.state column. Or, maybe what you really need is a view defined using the above mentioned query.

将视图票证状态创建为SELECT ticket.id ...

create view ticketstate as SELECT ticket.id ...

这篇关于简单的MySQL Join替换外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！