Problem with query

[toby007]

I have created a query to select data from a number of tables. (3 tables)

In summary I have 3 tables:

table 1 - girls - girlid, name
table 2 - rosters - rosterid, roster_date, start_time, finish_time
table 3 - girl_rosters - girl_rosterid, girlid, rosterid

1) I have created a query where I can retrieve all the girl names who are on
file and display the details on a web page.

2) I have created query where I can display the roster information for a
particular day

3) What I do want to do is a combination of both I want to display a list of
all girls, and I want to also indicate which girls are working on the day the
web site visitor is browsing the page.

I have created a query which is as follows:

SELECT dbo.girls.girlid, dbo.girls.girl_name, dbo.girls.girl_headline,
dbo.girls.girl_thumbnail1, dbo.girls_roster.girlid ,
dbo.girls_roster.rosterid
FROM dbo.girls
LEFT OUTER JOIN dbo.girls_roster
ON dbo.girls.girlid = dbo.girls_roster.girlid
LEFT OUTER JOIN dbo.Roster
ON dbo.girls_roster.rosterid = dbo.Roster.RosterID
Where dbo.girls.girl_active = 'Y' AND
RosterDate = #CreateODBCDate(todaysdate)#
ORDER BY 2 ASC

I have 10 girls stored in the girl table.

I only have 2 records in the roster table .

I am getting returned just 2 records - the records where the girl is on the
girl table and is also rostered to be working on a set date.

What I want to achieve is list all the girl records and if roster records
exist for a specific date indicate on the web page this information. I still
however want to show all 10 records irrespective of whether a girl is rostered
to be working.

So I want to show basically the following:

Alison - 18 year old student etc -
- Click here for more info

Bria - lovely blonde -
- Click here for more info

Carol - lovely redhead - Working today -
Click here for more info

Debbie - beautiful beach babe - -
Click here for more info

Any feedbacl on what needs changing in the SQL will be greatly appreciated.

Regards

P.S. I was under the impression Left Outer Join would pass all records etc
whether they contained info relating to the selection criteria.

[CF_Oracle]

What database do you use? If Oracle for example second Left Join would kick of
8 reecords because rosterid in both tables is Null. A solution could be first
to update NULLs in table girls_roster with 0 and change order of tables in your
SQL.

[toby007]

Hi all

I have modified the query which I thought was working OK but further testing
revealed that is was not working properly and I cant see why it isnt working
properly.

Basically I have 3 tables where I am trying to get information from.

Table 1 - Girls contains Girlid, name, pic
Table 2 - Rosters contains rosterid, rosterdate
Table 3 - Girls_Rosters contains girls_rostersid, girlid, rosterid

Table 3 is really only a link table to link the two tables together.

What I want to to is as follows:

List all the girls on a web page. A simple select would do this. What I
really want to do is do that but I also want to display to a person a note as
to whether the girl is working that day - not all girls will be working on a
given day.

So I want to show something like this

Girl Name Available Today


etc

I have a query which I developed. Whilst I get information out from this
query - for any girl who is rostered on for today I get to records outputted
from the query. The SQL is shown below.

Thanks in advance for feedback

SELECT dbo.girls.girlid, dbo.girls.girl_name, dbo.girls.girl_headline,
dbo.girls.girl_thumbnail1, dbo.girls_roster.girlid , Abi
Belinda X
Bobbie
Charlie X
Christine X
Debbie
Dianne
dbo.girls_roster.rosterid, dbo.Roster.RosterDate
FROM dbo.girls
LEFT OUTER JOIN dbo.girls_roster
ON dbo.girls.girlid = dbo.girls_roster.girlid
LEFT OUTER JOIN dbo.Roster
ON dbo.girls_roster.rosterid = dbo.Roster.RosterID
Where dbo.girls.girl_active = 'Y'
ORDER BY 2 girls.girl_name

Basically I am getting

Abi
Belinda
Belinda X
Bobbie
Charlie
Charlie X
Christine
Christine X
Debbie
Dianne

Any feedback would be greatly appreciated.