Joining tables in cfquery

[phamtum]

Any help is greatly appreciated, thank you in advanced for your help!

Database info:
There are 2 tables.
1. 1st table name: members
? id (pk)
? fname (first name)
? lname (last name)
? faddress (father address)
? fcity (father city)
? fzip (father zip)
? mon (yes/no available to work Monday)
? tue (yes/no available to work Tuesday)
? wed (yes/no available to work Wednesday)
? thu (yes/no available to work Thursday)
? fri (yes/no available to work Friday)
? sat (yes/no available to work Saturday)
? sun (yes/no available to work Sunday)

2. 2nd table name: book_05
? id (pk)
? mem_id (member id)
? b_date (date for booked for work ?mm/dd/yyyy?)
? details (descriptions of event)
? post_by (admin)
? posted (timestamp)

Table 1 member are all unique.
Table 2 a single member can have many booked work days.

What?s suppose to happen:
I want to query to return all member name that have not been booked to work
for the next day.

The problem:
The query return the name of a member that has been booked before as many time
as they have worked. I only need the name to show once, please see code example
below.

My code:
<cfparam name="startdate" default="#month(now())#/1/#year(now())#">
<cfset nextday=dateadd('d',1,now())>

<cfquery datasource="#db#" name="getSearch">
select m.id, m.fname, m.lname, m.faddress, m.fcity, m.fzip, b.mem_id, b.b_date
from members m left join book_05 b on m.id = b.mem_id
where id = id
AND b.b_date <> ###dateFormat(nextday, "mm/dd/yyyy")###
</cfquery>


<cfoutput>#getsearch.recordcount#</cfoutput><p> </p>
<cfoutput query="getSearch">
#fname# #lname#<br>
</cfoutput>



This is the out put:

6
Michelle Johnson
Eric Del Rosario
George Perez
George Perez
Veronica Gonzales
Veronica Gonzales

George Perez and Veronica Gonzales should only come up once. Even thought they
have been booked to work twice before. I just want to know if they are booked
for the next day.

[paross1]

Why are you doing this (where id = id) in your query?

<cfquery datasource="#db#" name="getSearch">
select m.id, m.fname, m.lname, m.faddress, m.fcity, m.fzip, b.mem_id, b.b_date
from members m left join book_05 b on m.id = b.mem_id
where id = id
AND b.b_date <> ###dateFormat(nextday, "mm/dd/yyyy")###
</cfquery>

[phamtum]

Please ignore the where id = id bit as I have loads of <CFIF statem,ents after for using this query as a search results generator. You can remove it if you like.

[mxstu]

I think there is another problem besides the dupes. Let's say there is a
member that has never been booked before (i.e. there are no records for this
member in the [book_05] table). That member will not be included in the
results of your query.

You're using a LEFT JOIN, to include all members (even if they have no
bookings). Yet, in the WHERE clause you reference the booking date [b_date],
which effectively excludes any member (that has never been booked) from the
results.

If you want to include all members in the results (even if they have never
been booked), then I think you're looking for something like the query below. I
don't know what database/connection type you are using. You may need to modify
the syntax.



select m.id, m.fname, m.lname, m.faddress,
m.fcity, m.fzip, b.mem_id
from members m left join book_05 b ON
( m.id = b.mem_id and b.b_date = #CreateODBCDate(nextday)# )
where b.b_date is null

[phamtum]

It works, Thanks MXSTU you're the man!!! =D

[phamtum]

MXSTU,
I hope you can help answer this question.

The code above work great! But, now that I'm introducing another table that
also needs to be join to the members table I'm getting an error "Missing
expressions". The new tables' name is talents and the PK is mem_id only one row
per member.

Here is my code:
select m.id, m.fname, m.lname, m.faddress, m.fcity, m.fzip, b.mem_id
from members m left join book_05 b ON ( m.id = b.mem_id and b.b_date =
#CreateODBCDate(nextday)# ),
members m left join talents t on m.is = t.mem_id
where b.b_date is null

Thank you in advanced fopr your assistance!

[mxstu]

The syntax of your FROM clause is a little off. I did not test this code, but
I think what you're looking for is something like this. Modify it accordingly
for you database type. You may want to review your database documentation for
the exact JOIN syntax

select m.id, m.fname, m.lname, m.faddress
from ( (members m left join book_05 b ON
( m.id = b.mem_id and b.b_date = #CreateODBCDate(nextday)#) )
left join talents t on m.id = t.mem_id
where b.b_date is null

Is a LEFT JOIN with the Talents table what you want (i.e. return all member
records even if there is no matching record in the Talents table)?

[phamtum]

Brilliant!!!
You have knocked every curved ball that I?ve thrown at you out of the park! Thank you very much for your expertise advises!!!