CODEWITHSUNDEEP
mysql
Allan
Allan

Reputation: 211

Multiple "where" -s from one table into one view

I have a table called "users" with 4 fields: ID, UNAME, NAME, SHOW_NAME.

I wish to put this data into one view so that if SHOW_NAME is not set, "UNAME" should be selected as "NAME", otherwise "NAME".

My current query:

SELECT id AS id, uname AS name 
FROM users 
WHERE show_name != 1 
 UNION 
SELECT id AS id, name AS name 
FROM users 
WHERE show_name = 1

This generally works, but it does seem to lose the primary key (NaviCat telling me "users_view does not have a primary key...") - which I think is bad.

Is there a better way?

Upvotes: 0

Views: 60

Answers (3)

paxdiablo
paxdiablo

Reputation: 881403

That should be fine. I'm not sure why it's complaining about the loss of a primary key.

I will offer one piece of advice. When you know that there can be no duplicates in your union (such as the two parts being when x = 1 and when x != 1), you should use union all.

The union clause will attempt to remove duplicates which, in this case, is a waste of time.

If you want more targeted assistance, it's probably best if you post the details of the view and the underlying table. Views themselves don't tend to have primary keys or indexes, relying instead on the underlying tables.

So this may well be a problem with your "NaviCat" product (whatever that is) expecting to see a primary key (in other words, it's not built very well for views).

Upvotes: 1

ajacian81
ajacian81

Reputation: 7569

This can likely be better written as the following:

SELECT id AS id, IF(show_name == 1, name, uname) AS name FROM users

Upvotes: 1

Jagmag
Jagmag

Reputation: 10356

If i am understanding your question correctly, you should be able to just use a CASE statement like below for your logic

SELECT 
    CASE WHEN SHOW_NAME ==1 THEN NAME ELSE UNAME END
FROM users

Upvotes: 1

Related Questions