I was hoping I could get someone's help on some SQL that is stumping me.
When I connect this view table and another table with an outer join, it is not doing the outer join. It's doing it like an inner join in the data result I see in cfdump.

The idea is to show someone ALL of galleryinfo records (DSID is the primary key of the gallery record), but in the display, there is also a column where IF they have seen that record (vs. not), it tells them. I have to get this via query so I can use CFGRID rather than looping through with subqueries.

Table1 (view): galleryinfo (columns: DSID, alias) (gallery record, gallery author)
Table2 (normal): galleryseen (columns: DSID, seenUID) (gallery record ref, onlooker userID)

I need all records from table1, but only the records from table 2 where the UID matches that of the user looking at it. I need a 'null' value (as usual with outer joins) if there is no seen record.

SELECT
g.DSID, g.alias, s.seenUID

FROM
galleryinfo g LEFT OUTER JOIN galleryseen s
ON
g.DSID = s.DSID

WHERE
seenUID = #session.uid#


What it shows me when I run this query with CFDUMP, is *only* a recordset that has a matching galleryseen record with my userid (seenUID) in it. But it's supposed to be including the galleryinfo records I haven't seen, too (has no record in galleryseen for my UID).

I don't understand what I'm doing wrong. I've been writing JOINs for years and it's usually not this hard. It SAYS 'left outer join' but that doesn't seem to be the result I'm getting. This is more like an inner join where it parses out records that don't have a match.

Does anybody see anything that I might be missing? Maybe I'm just dense today. Many thanks for any assistance.

PJ

You have probably figured this out by now. But I suspect the problem was your where clause. By referencing table2 (galleryseen) in that manner, you limit the results to only records with the seenUID you specified. Which essentially negates the left join, turning it into an inner join instead.

WHERE seenUID = #session.uid#

To also include records that have no match try

WHERE seenUID = #session.uid# OR seenUID IS NULL

You should also consider using cfqueryparam for all query parameters.