SQL Same Column in one row

Question

I have a lookup table that has a Name and an ID in it. Example:

ID                                                  NAME
-----------------------------------------------------------
5499EFC9-925C-4856-A8DC-ACDBB9D0035E             CANCELLED
D1E31B18-1A98-4E1A-90DA-E6A3684AD5B0                31PR

The first record indicates and order status. The next indicates a service type.

In a query from an orders table I do the following:

INNER JOIN order.id = lut.Statusid

This returns the 'cancelled' name from my lookup table. I also need the service type in the same row. This is connected in the order table by the orders.serviceid How would I go about doing this?

It Cancelled doesnt connect to 31PR.

Orders connects to both. Orders has 2 fields in it called Servicetypeid and orderstatusid. That is how those 2 connect to the order. I need to return both names in the same order row.

Answer 1

I think many will tell you that having two different pieces of data in the same column violates first normal form. There is a reason why having one lookup table to rule them all is a bad idea. However, you can do something like the following:

Select  ..
From order
    Join lut
        On lut.Id = order.StatusId
    Left Join lut As l2
        On l2.id = order.ServiceTypeId

If order.ServiceTypeId (or whatever the column is named) is not nullable, then you can use a Join (inner join) instead.

Answer 2

A lot of info left out, but here it goes:

SELECT orders.id, lut1.Name AS OrderStatus, lut2.Name AS ServiceType
FROM orders
    INNER JOIN lut lut1 ON  order.id = lut.Statusid
    INNER JOIN lut lut2 ON order.serviceid = lut.Statusid