Self join eliminates records when field is empty 1

[Faded]

Hello,

I am doing a self join two times in one query, and I need to show all records even if one of the fields is not existent. Hard to explain, but here's the query.

SELECT mainTitle.pk_TitleID, mainTitle.content, altTitle.content, physTitle.content, mainTitle.fk_ObjectID
FROM Title AS mainTitle, Title AS altTitle, Title AS physTitle, Object AS o
WHERE mainTitle.fk_ObjectID=o.pk_ObjectID
AND mainTitle.fk_TitleTypeID=1
AND altTitle.fk_ObjectID=o.pk_ObjectID
AND altTitle.fk_TitleTypeID=2
AND physTitle.fk_ObjectID=o.pk_ObjectID
AND physTitle.fk_TitleTypeID=3;

In the FROM section you can see the table "Title" is aliased three times - mainTitle, altTitle, and PhysTitle and they reference the primary key in the "Object" table. The "Title" table contains approx. three times the entries that the "Object" table does because of the foreign key fk_TitleTypeID that references the "TitleType" table which contains 1 mainTitle, 2 altTitle, 3 physTitle.

The problem is, if there is a mainTitle and a physTitle but no altTitle for an object, the record is not listed at all in the selected records. I want it to show the item with the mainTitle and physTitle, and just leave the altTitle blank.

I will be happy to provide more info if you need it, but I don't want this post to be more confusing than it already is. I would appreciate any help on getting this query working properly.

Thanks, Fadad

 

[rac2]

Hello Faded,

Use an outer join to obtain records when the second table is missing rows to match with the first table.

Let me re-write your query using the JOIN syntax, then I will write it with an outer, or LEFT, join. Also it seems natural to me to start with the Object table so I will do it that way.

SELECT mainTitle.pk_TitleID, mainTitle.content, altTitle.content, physTitle.content, mainTitle.fk_ObjectID

FROM Object o

JOIN Title mainTitle ON mainTitle.fk_ObjectID=o.pk_ObjectID
AND mainTitle.fk_TitleTypeID=1

JOIN Title altTitle ON altTitle.fk_ObjectID=o.pk_ObjectID
AND altTitle.fk_TitleTypeID=2

JOIN Title physTitle ON physTitle.fk_ObjectID=o.pk_ObjectID
AND physTitle.fk_TitleTypeID=3

And here is the outer join.

SELECT mainTitle.pk_TitleID, mainTitle.content, altTitle.content, physTitle.content, mainTitle.fk_ObjectID

FROM Object o

LEFT JOIN Title mainTitle ON mainTitle.fk_ObjectID=o.pk_ObjectID
AND mainTitle.fk_TitleTypeID=1

LEFT JOIN Title altTitle ON altTitle.fk_ObjectID=o.pk_ObjectID
AND altTitle.fk_TitleTypeID=2

LEFT JOIN Title physTitle ON physTitle.fk_ObjectID=o.pk_ObjectID
AND physTitle.fk_TitleTypeID=3

Barring any syntax erros, typing errors, and falty assumptions about the structure of your tables, this should give you a row for every row in the Object table whether or not there are values for the various title types. At least I think so.

 

[Faded]

Thanks for your help, but when I try to run this query I get:

Syntax error (missing operator) in query expression ".

and the cursor goes to:

LEFT JOIN Title AS mainTitle ON mainTitle.fk_ObjectID=o.pk_ObjectID
AND mainTitle.fk_TitleTypeID=1

I have entered exactly what you have given, and I can't seem to spot the error.

Am I missing something obvious?

Thanks, Faded

 

[Faded]

Hello again,

I posted the last post a few minutes too early, and once I finally woke up I realized what was happening.

You can't SELECT mainTitle.pk_TitleID, mainTitle.content, altTitle.content, physTitle.content, mainTitle.fk_ObjectID
FROM Object o because that table doesn't contain all of those fields. The object table is just an ID table in a snowflake design database. Here is the way I have the tables laid out.

Object:
pk_ObjectID content
1 image
2 anotherimage
3 yetanotherimage

Title:
pk_TitleID fk_ObjectID content fk_TitleTypeID
1 1 An Apple 1
2 1 Eating a Macintosh 2
3 1 \images\apple.jpg 3
4 2 An Orange 1
5 2 Slicing the Orange 2
6 2 \images\orange.jpg 3
etc...

TitleType:
pk_TitleTypeID content
1 Main Title
2 Alternate Title
3 Physical Title

I hope this helps clarify how the tables are set up, and what I am trying to draw from the query.

Cheers, Faded