Problem with sql query. Should i be using union?

Question

Have a problem that seems easy on paper but i'm having a big problem figuring out how best to write a single query.

I have a table

CREATE TABLE `profile_values` (  
  `fid` int(10) unsigned NOT NULL default '0',  
  `uid` int(10) unsigned NOT NULL default '0',  
  `value` text,  
  PRIMARY KEY  (`uid`,`fid`),  
  KEY `fid` (`fid`)  
) 

The point of this table is to store profile information on a user.
E.g. a typically row would look like this..

fid | uid | value  
__________________
1   | 77  | Mary  
11  | 77  | Poppins  
1   | 123 | Steve  
11  | 123 | Davis  

Note:

'fid' of '1' represents the first name  
'fid' of '11' represents the last name  
'uid' is a users id within the site.  

What I am trying to achieve is to bring back all uid's that satisfy the condition of first name like 'S%' and last name like 'D%'.
The reason i need this is because i have an autocomplete search box for users on the site.
So if i type 'S' in the search box i will see list of all users that begin with the letter 'S', if i now type a whitespace and 'D' i should now be able to see the list of users who match both conditions.

Does anyone have any idea how this can be accomplished? Thanks in advance.

Answer 1

select firstname.uid, fistname.value, lastname.value
from 
  profile_values firstname
  inner join profile_values lastname 
    on firstname.uid = lastname.uid
WHERE
  firstname.fid = 1
  AND lastname.fid = 11
  AND fistname.value like 'S%'
  AND lastname.value like 'D%'

or, with a users table

select users.uid, fistname.value, lastname.value
from 
  users
  inner join profile_values firstname
    on firstname.uid = users.uid
    AND firstname.fid = 1   
  inner join profile_values lastname 
    on lastname.uid = users.uid
    AND lastname.fid = 11
WHERE
  fistname.value like 'S%'
  AND lastname.value like 'D%'

EDIT:

Forgot to mention: union performs bad on many dbms. If you need a union, there is mostly something wrong with your database model. Use any other alternative before using union.

Answer 2

SELECT  *
FROM    profile_values pv1
WHERE   pv1.fid = 1
        AND pv1.value LIKE 'M%'
JOIN    profile_values pv11
ON      pv11.fid = 11
        AND pv11.value LIKE 'D%'
        AND pv11.uid = pv1.id

Answer 3

Join on profile_values twice like this:

SELECT p1.uid
FROM profile_values p1, profile_values p2
WHERE p1.uid=p2.uid AND p1.fid=1 AND p2.fid=11
  AND p1.value LIKE 'S%'
  AND p2.value LIKE 'D%'

Answer 4

The table you've shown looks like a classic example of the inner-platform effect anti-pattern.

So rather than answering your question in the short term (which has already been done), I'll answer that in the longer term your goal should probably be to change the database so that first name and last name are stored in their own columns. These can then be indexed so this type of search will be much faster, and they'll be much easier to work with as you can just use the column name directly.

The fact that you use numbers to refer to the type of data stored in the column indicates that there is a finite number of types of data actually in use, which means they could be broken out into separate columns (if space is a concern and many values are null then you may be able to use sparse columns).

Then your query would simply be something like:

select * from users where first_name like 's%' and last_name like 'd%'