selecting multiple rows by different data in same column

fikse

in the following users table:

this doesn't work:

SELECT DISTINCT user FROM users WHERE type = 'admin' AND type = 'regular'

thanks for any help....

eoghain

SELECT DISTINCT user
  FROM users
 WHERE type = 'admin'
    OR type = 'regular'

Since type can only be either admin or regular an AND won't work. It can't be both so you need to use an OR.

fikse

the user can be both.....

is there any way to select all users that are have records as admin and regular?

thanks

cgraz

On your example under the type column, there is only admin or regular. If they can be both, how do you input this in the database? If you inputted it as admin_regular, then

SELECT DISTINCT user
  FROM users
 WHERE type = 'admin'
    OR type = 'regular' OR type = 'admin_regular'

fikse

the table I put in above is just a sample... it is way over simplified.... ignore what admin and regular actually means..... there will be many more designations for the type.....

for this simplified query, I just need to select all users that are have records as admin and regular......

thanks

eoghain

What are valid values for the type column?

Your sample data shows that you have 2 records for a user 1 is a 'regular' type and 1 is a 'admin' type. This to me means that the value in the type field can be either 'regular' or 'admin' not both since you don't have any records of the type 'regular admin'.

Ok, I just looked over my query and I see what you are asking for that I didn't provide. You want only users that have both a 'regular' type record and a 'admin' type record. My query would return to you all users of type 'regular' and all users of type 'admin' which would get you more users than you want.

Try this:

SELECT user
  FROM users
 WHERE type = 'admin'
    OR type = 'regular' 
 GROUP BY user
HAVING count(*) = 2

fikse

thanks for the help.... your code does work.... but, I'm realizing that I might have to go back to the database planning board.....

I won't know ahead of time how many records to expect.... which means the count(*) won't work later on when the table gets filled with records...

each user might have 10 or 20 records with the type = admin in the table... and a few with the type = regular.... I basically need to know which users have at least one record that is admin and one that is regular....

thanks

eoghain

Then you definatly need to go back to the planning stage. I don't know anything about your database or project so it's very hard to offer suggestions about it, but what I try to do is always minimize the amount of duplicate data that I have to store. So if I have a user who could be multiple types of a user I'd usually build a cross referance table that holds what types the user is.

users
-----
user_id int
user_name varchar(50)

types
-----
type_id int
type_name varchar(20)

user_x_types
-------------
user_x_type_id int
user_id int
type_id int

This would give me the ability to get a list of users of any given type or combination of types. If I needed to assign a user as a type to a specific item then I could have another cross referance table that handled that:

message_boards
--------------
message_board_id int
message_board_name varchar(50)

uxt_x_message_boards
--------------------
uxt_x_message_board_id int
message_board_id int
user_x_type_id int

So here I have a table holding my message boards and then I link users to a given message board by type. This way I can see that the type of user that 'john' is for this message board is 'regular' and so I won't show him the admin tools. But for another message board 'john' is the admin and will get the admin tools displayed.

fikse

thanks.... since I over simplified things to get right at what I needed, it's hard to see exactly what I'm trying to do..... you answered another one of my posts that went into more detail, and mysql might not be able to do the query I need....

basically, I have a table of users..... I'm trying to track each users progresss through an online course....

I have another table setup that records the page, time, IP address, browser, as the user browses throughout the site.... they could visit the same page twice, which is fine, which is why there will be muliple rows in the table, with the same user, and same page.....

I need for site admins to do a query on the tables to show which users have completed the online course..... by showing them who has visited EACH of the pages at least once....

So the query would ask, in laymens terms:

How many users have been to page01, page02, page03, page04, etc.. etc.. AT least once.....

any ideas?

thanks

eoghain

You might want to think about re-designing that as well. Unless you can impliment an INTERSECT query like I showed here you might be out of luck. That is if you want to have the database do all of the work. You can always just select everything from the logging table and then parse through it yourself with PHP.

fikse

thanks again for the insight....

another idea I had was to use a merge table...

maybe I could add additional columns in my users table, page01, page02, etc.. etc..

in each of the pagexx columns would be a merge that would be a query in itself: SELECT count(*) FROM usage WHERE userid = 'userid' and page = 'pagexx'......

maybe this isn't a merge.... is there a way to have the value of a column actually be a query of a different table?

thanks again...

eoghain

I wouldn't recommend doing either of these.

Having extra columns that may never hold data is not good design practice.

If you have to have the tables designed the way they are then I suggest that you just use PHP code to do the other work.