Something about Qualifiers?

[blyxx86]

Good afternoon everyone...

I'm trying to remove unnecessary NULL values from within some of my tables.

Essentially I have this problem: An item can be assigned to a person/location/status. However, it is only in one of those places, never all three, and never a combination of them. I could define 3 columns person_id, location_id and status_id. Then generate 3 queries to find who/where/what that item is assigned to. ORRRR... Is it possible to put a qualifying column that would be an enum ("person", "location", "status") and the ID would go with the other part.

Any ideas on what I'm looking for? I'm kinda stumped.

[code green]

What about a table

assign     code
person       1
location     2
status       3

Then have TWO columns for assigning, which will never be NULL
That is an ID column which holds the person_id of location_id etc
and id_code linked to table above.

Or have I missed your point completely?

[blyxx86]

No no. You hit my question right on the head.

You said, much more clearly, what my "ORRR" section was attempting to explain. How, then, would I write a query when each of those ids relate to a different table?

[code green]

Ouch. So the IDs are in different tables.
I think is what is referred to as a conditional JOIN.
I have had a similar problem. Can't remember if I solved it.

Try googling for conditional JOIN while I think about this.

[blyxx86]

I found something regarding CASE statement and MySQL.

http://dev.mysql.com/doc/refman/5.0/...statement.html

SELECT (CASE field1 WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) FROM mytable;

I'm super tired (yet suffering from a slight case of insomnia). So I'm mostly posting this find as a note source for sometime tomorrow or over the weekend.