Re: Query Join no unique link

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Daniel P. (danutzp1_at_)
Date: 05/06/04 

Date: Thu, 6 May 2004 17:33:24 -0500

NrSeq seems to be different but if you do not care about that then one
solution could be:

select *
    from TabA a
    left join
    (
        SELECT Police, MIN(NrSeq) AS NrSeq, Amt, cd
        FROM TabB
        GROUP BY Police, Amt, cd
    ) AS b
    on (a.police and b.police and a.amt = b.amt and b.cd = 6)
where TABA.code = 9

If you do not like MIN you can use MAX. Anyway you need to find a way to get
only one record from the second table.

Or you can just disregard the TabB . NrSeq field if you do not need it:

select *
    from TabA a
    left join
    (
        SELECT DISTINCT Police, Amt, cd
        FROM TabB
        GROUP BY Police, Amt, cd
    ) AS b
    on (a.police and b.police and a.amt = b.amt and b.cd = 6)
where TABA.code = 9

"Nic" <anonymous@discussions.microsoft.com> wrote in message
news:35474AE6-C60A-4D1C-8010-63623E5BEA01@microsoft.com...
>
> TabA (expected payments) TabB (payments)
> Police NrSeq Amt Cd Police NrSeq Amt cd
> A 10 20 9 A 34 20 6
> A 11 20 9 A 40 20 6
>
> I want to join this 2 tables, but I don't want to join the same record of
tabB twice.
> I have this Query and it is not what I want. Each expected payment, can
have only one payment (the amount of the recuurrent payment is the same)
>
> select * from TabA a left join TabB b on (a.police and b.police and
a.amt = b.amt and b.cd = 6)
> where TABA.code = 9
>
> Results
> A 10 20 9 A 34 20 6
> A 10 20 9 A 40 20 6
> A 11 20 9 A 34 20 6
> A 11 20 9 A 40 20 6
>
> But I don't want to dubble link. The results I want are
> A 10 20 9 A 34 20 6
> A 11 20 9 A 34 20 6
> On record in tabA with one record in tabB, and not with 2 records of tabB
(yes the join fields are the same, but I don't have a unique link between
that is my porblem)
>
> So, my question is how can I join 2 tables with no unique link to each
other. But in each table I may use each record one time to link.
>
> Thanks,
> Nick
>
>

Relevant Pages

  • Update TabA with value fromTabB
    ... TabA is a Detail Table, Col A belong to TabA ... TabB is a Header Table, ...
    (microsoft.public.sqlserver.programming)
  • Variables Update einer Tabelle
    ... ich habe schwierigkeiten eine Update-Klausel zu formulieren. ... Tabellen (TabA, TabB). ... Ich formuliers mal in Worten: ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: Joins... Urgent
    ... >FROM TabA left join TabB on TabA.FldA = TabB.FldA ... If there is no record in TabB that matches any record in TabA, ... Come for live chats every Tuesday and Thursday ...
    (microsoft.public.access.queries)
  • Re: SQL: getting data for a null value column ??
    ... Thanks hrishy san ... so i have to take name from tabB only. ... PS: about union ... FROM taba a,tabb b where b.bbankcode=a.bankcode ...
    (comp.databases.oracle.server)
  • Re: SQL: getting data for a null value column ??
    ... > so i have to take name from tabB only. ... > o/p what i want is (without using union) ... > FROM taba a,tabb b where b.bbankcode=a.bankcode ... The UNION construct in it's basic form will select *all* ...
    (comp.databases.oracle.server)