Re: nasty SQL query, please advise...

"swansnow" <schultz@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1127224435.032621.228340@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> Three tables: clients, coverage, visits
> A client may have one or more coverages. A coverage may have one or
> more visits.
>
> Clients pk: clientID
> coverage pk: covID, fk:clientID
> visits pk: visitID, fk:covID
>
> The query is this:
> The visits table needs to store the client number for the client whose
> visit this is for (don't ask why...)
>
> I'm not sure how to express the SQL. Here's a beginning:
>
> UPDATE visits
> SET clientnum = (SELECT clientID from clients c
> LEFT JOIN coverage v on c.clientID = v.clientID
> LEFT JOIN visits t on v.covID = t.covID
> WHERE .....???? )
>
> I don't know what to put in the WHERE clause.

(visits.visitID = t.visitID). I think. You may need an alias on the
Visits table as referenced by the UPDATE query itself, to disambiguate
it from t.

What you want is to update only the record from where you started
joining.

Can't you UPDATE a JOIN directly? That way, you wouldn't need a WHERE
clause at all. I think.

Groetjes,
Maarten Wiltink


.

Relevant Pages

  • Re: www.petassure.com
    ... It's like any insurance coverage - a gamble that you won't need it. ... of clients vs. outgo of clients' coverage). ... If the $6.50 is because your company is subsidizing it, ...
    (rec.pets.cats.anecdotes)
  • nasty SQL query, please advise...
    ... Three tables: clients, coverage, visits ... coverage pk: covID, fk:clientID ... Prev by Date: ...
    (comp.lang.pascal.delphi.misc)
  • Re: Best wifi location?
    ... I've had clients that love the wifi at Coronado Springs. ... Coverage is pretty good, although not ...
    (rec.arts.disney.parks)
  • Re: Canadian outsourcing - did not know this
    ... If I lose clients over it so be it! ... Barb - just holler if you need some coverage. ...
    (sci.med.transcription)
  • Re: NOT IN with a subquery
    ... If there is a NULL in the list for the IN clause, ... ELSE SELECT 'UNKNOWN' ... >> This query returns 155 rows: ... >> select distinct dp_source from clients ...
    (microsoft.public.sqlserver.programming)