Query Problem: Field doesn't always exist

[RuBot]

I have 3 tables, a Survey table, a Ticket table, and a Notes Table. The notes
table contains notes for the Ticket, and the Survey is a questionairre about
the Ticket.

Not every Ticket has Notes. I'm creating a site that displays the Surveys, and
related Ticket and Note information. I need it to query the Note table if
applicable.

Let's say I have
<cfquery name = "name" datasource = "db">
select a.*, b.* from ticket a, survey b
where a.ticketid = b.ticketid
</cfquery>

that will only get the surveys and tickets, which will always have matching
fields.

<cfquery name = "name" datasource = "db">
select a.*, b.*, c.*
from ticket a, survey b, notes c
where a.ticketid = b.ticketid and a.ticketid = c.ticketid
</cfquery>
This will only pull records with notes. How do I get records with notes, and
without??

I seem to have this type of query problem often. Any suggestions?:brokenheart;

[mxstu]

Use a left join to retrieve all of the records from one table even if there
are no related records in the other. Something like ...

--- not tested. adjust syntax for your database type.
SELECT someColumns
FROM ticket a INNER JOIN survey b ON a.ticketid = b.ticketid
LEFT JOIN notes c ON a.ticketid = c.ticketid

[RuBot]

Tables - Survey, Ticket, Notes

Every survey record matches a ticket record, but not all ticket records have a
matching notes record.

Conceptually, isn't the join you listed still looking to match a ticket to a
note? Will this still include records that do not have a matching field?

Sorry I'm a gomer sometimes.

[Kronin555]

What mxstu posted will do exactly what you're asking for.

LEFT JOIN is also sometimes referred to as LEFT OUTER JOIN, but the OUTER is
inferred. Basically, it's like adding a row to the Notes table that matches
anything in the other table that doesn't already have a match in the Notes
table. Oracle accomplished this with the (+) syntax.

Here's some more reading on LEFT JOINs:
[url]http://www.w3schools.com/sql/sql_join.asp[/url]
[url]http://www.devguru.com/Technologies/jetsql/quickref/leftjoin.html[/url]

[paross1]

FYI, If you happen to be using a version of Oracle before 9.x, you can not use
the JOIN syntax, but you can use the old proprietary (+) notation in the WHERE
clause for the OUTER join. (Oracle 9 and above, you can use the ANSI-SQL 92
JOIN syntax.)

Something like this:

SELECT someColumns
FROM ticket a, survey b, notes c
WHERE a.ticketid = b.ticketid
AND a.ticketid = c.ticketid(+)

Phil

[RuBot]

I have one thing to add: I'm using Microsoft Access to accomplish this task, so I'm thinking the (+) syntax isn't going to work.

Any ideas?

[RuBot]

Oh, do what mxstu said right? Let me try that and I'll get back to everyone

[RuBot]

Here's my Query, and here's my error:

<cfquery name = "getSurveys" datasource="HelpDesk">
select a.*, b.*, c.*
from survey a inner join Help_Desk_List b
on a.ticketID = b.entry_ID
left join notestbl c
on a.ticketID = c.ticket_ID
</cfquery>

"Syntax error (missing operator) in query expression 'a.ticketID = b.entry_ID
left join notestbl c on a.ticketID = c.ticket_ID'. "

An added bonus to this problem is that I have duplicate ticketIDs in the
survey table '0' which represents random surveys generated unassociated with
any tickets.

I plan on doing this:

<cfquery name = 'getSurvTicketID' datasource = 'HelpDesk'>
select ticketID from survey
</cfquery>
<cfloop query = "getSurvTicketID">
<cfif ticketID neq 0>
<cfquery name = "getSurveys" datasource="HelpDesk">
select a.*, b.*, c.*
from survey a inner join Help_Desk_List b
on a.ticketID = b.entry_ID
left join notestbl c
on a.ticketID = c.ticket_ID
</cfquery>
</cfif>
</cfloop>

[mxstu]

>"Syntax error (missing operator)
If you're using Access, I think it requires parenthesis around multiple join
statements. Your best bet is to check your Access documentation. It is
probably something like ....


Select ....
from (survey a inner join Help_Desk_List b
on a.ticketID = b.entry_ID)
left join notestbl c
on a.ticketID = c.ticket_ID

>An added bonus to this problem is that I have duplicate ticketIDs in the

survey

>table '0' which represents random surveys generated unassociated with any

tickets.

>I plan on doing this:

Why loop? If you just want to exclude records where the ticketID value = '0',
then add a where clause to your query...


SELECT ......
FROM survey a .....
WHERE a.ticketID <> '0'

[RuBot]

It turns out that I can't try this because........the ticketID field in the
Survey table is actually a string because I use a date/time (mmddyyhhmmss) for
random survey id generation, and it won't save as a long.

Have any ideas for a better random id generator that won't generate duplicates?

[paross1]

Have any ideas for a better random id generator that won't generate duplicates?

With Access, you can use autonumber.

Phil

[RuBot]

Yeah, I have the autonumber set as the survey's id, and within each record it
contains the ticketID which is the foreign link to the autonumbered entry_ID in
the help_desk_list table, which in turn links to the autonumbered ticketID in
the notes table, if a record exists.

[RuBot]

Well, here's what I did. I changed the field back to numeric and used mmddhhmmss.