multiple WHERE clauses in one recordset

[mgn2683]

How would I go about creating a recordset with two WHERE clauses in it? Basically, I have a list and detail pages for a team assignment. In the list page, there are two fields that have to be concatenated to show full names. In the user table they have different user levels, but I'm having trouble displaying the second field as f_name. Here is my recordset:

SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name
FROM assignment, `tbl_user`
WHERE tbl_user.Id= assignment.Reader

I need to add a second WHERE clause that would be

WHERE tbl_user.Id= assignment.Chaplain

Anyone have any suggestions?

[dickersonka]

WHERE tbl_user.Id= assignment.Reader OR tbl_user.Id = assignment.Chaplain

i think this is what you are looking for with the additional where

[mgn2683]

It is, but I need to display the return as a separate field if that makes sense. Basically I'm using this recordset to produce columns for a list page(dynamic text), and I need to somehow differentiate the two f_name fields. I need one f_name for both Readers and Chaplains, is this possible? I've tried renaming it reader_f_name or f_name_chaplain but neither are working.

[dickersonka]

f_name field is just pre + first + last + suffix

it will use the same formatting for both readers and chaplains

if you want this field different, please explain more clearly with an example of what you need

[mgn2683]

Its hard to describe this I guess. I'm using Developer Toolbox to create the list & detail pages, so my problem is differentiating the f_name for Readers and Chaplains. Like for the Readers column, right now its set to rs_assignment.f_name

And it Concats pre, first, last and suffix like you said. But since I need the same concat for Chaplains in the same recordset, it is hard to create a f_name field for Chaplains. If I set both Reader and Chaplain columns to rs_assignment.f_name, it will display all Readers and Chaplains in both of the columns.

Here is my current recordset:

SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name
FROM assignment, `tbl_user`
WHERE assignment.Reader= tbl_user.Id 
UNION
SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name
FROM assignment, `tbl_user`
WHERE assignment.Chaplain= tbl_user.Id

Is it possible to rename f_name to make it apply to a certain column, such as f_name_chp?

[dickersonka]

i don't get what the difference is in the concat of the chaplain, you are using the same columns

can you show what the concat difference is?

[mgn2683]

Well the difference is they are different columns in the database, and therefore have different names. I need to display both the readers and chaplains full names. However, since there are two WHERE clauses, one for readers and one for chaplains, the list page is showing all 80 names, 40 readers and 40 chaplains.

I need to show only the 40 readers for the readers column, and only the 40 chaplains for the chaplain column. This is where I am running into problems, because both Concats are named f_name.

[dickersonka]

If you need the results separated, then why not issue 2 queries?

Or add an additional column to the rows for the union,
for readers 0 as IS_CHAPLAIN
for chaplains 1 as IS_CHAPLAIN

[mgn2683]

I've already tried two queries, it keeps messing up the results. Where in the query would I insert this?

for readers 0 as IS_CHAPLAIN
for chaplains 1 as IS_CHAPLAIN

And sorry, I'm still a novice at php, what does this do?

[dickersonka]

SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name,
0 as is_chaplain
FROM assignment, `tbl_user`
WHERE assignment.Reader= tbl_user.Id 
UNION
SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name,
1 as is_chaplain
FROM assignment, `tbl_user`
WHERE assignment.Chaplain= tbl_user.Id

this will allow you to have a record set where you could check if the is_chaplain =1 then add to your chaplain list otherwise add to the readers list

[mgn2683]

Thanks for all your help thus far. Now there is a column is_chaplain, but unfortunately there was no data in it. All 80 records were listed under the f_name column still. Any ideas how to populate the is_chaplain column with the info?

[dickersonka]

i think we need to add single quotes around the is_chaplain

//readers
0 as 'is_chaplain'

//chaplains
1 as 'is_chaplain'

[mgn2683]

Ok, now I have all zeroes and ones in the is_chaplain column. I guess the remaining issue is getting the query to identify who is a chaplain. We are already pulling from two tables, and Readers and Chaplains have 2 different user levels in the user table (2 & 3). Could we make the tbl_user.user_level part of the query? That would differentiate readers from chaplains, then the WHERE would restrict which Id's match between the two tables.

[dickersonka]

yes, i thought you meant you didn't have a way to distinguish

instead of the is_chaplain, substitute the user_level into the query

[mgn2683]

I'm still only getting 0 or 1 in the user_level1 column.

SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.user_level, tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name,
0 as 'user_level'
FROM assignment, `tbl_user`
WHERE assignment.Reader= tbl_user.Id 
UNION
SELECT assignment.Id, assignment.Candidate, assignment.`Section`, assignment.Team, assignment.Chaplain, assignment.Reader,  tbl_user.user_level, tbl_user.Id,  CONCAT_WS(' ', `tbl_user`.name_pre, `tbl_user`.name_first, `tbl_user`.name_last, `tbl_user`.name_suffix)AS f_name,
1 as 'user_level'
FROM assignment, `tbl_user`
WHERE assignment.Chaplain= tbl_user.Id

I feel as if we are missing one thing then this will work.

[dickersonka]

thats because you are assigning it, the is_chaplain column didn't exist and we had to give it values, the user level column does exist

use this in both queries and you will get your 2 or 3

..... AS f_name,
`tbl_user`.user_level
FROM assignment,
......

[mgn2683]

Ok, now that I can differentiate the readers and chaplains, how would I get the f_name to apply to the new column? That was my whole reason for posting here, to be able to get f_name for both chaplains and readers in two separate columns.

[dickersonka]

That is the point of the query, you are able to distinguish in the same resultset of who are chaplains and not. You want to use the same column and distinguish code side by checking user_level.

[mgn2683]

Yea! I finally got it to work correctly. Here was my recordset

SELECT assignment.Id, assignment.Candidate, assignment.Section, assignment.Team, assignment.Chaplain, assignment.Reader,  CONCAT_WS(' ', tbl_user.name_first, tbl_user.name_last)AS f_name, CONCAT_WS(' ',tbl_user_1.name_first, tbl_user_1.name_last)AS full_name
FROM (assignment INNER JOIN tbl_user ON assignment.Chaplain = tbl_user.Id) INNER JOIN tbl_user AS tbl_user_1 ON assignment.Reader = tbl_user_1.Id