real-world query - too difficult for me!

14 Jul 2006 6:46 AM

Jim Lawton

I have to resolve this problem - I have various ways I can do it, but I
don't know enough SQL to do it in a single query (I have to do that,
because the solution is cross-platform, and has to wrk where stored
procedures aren't an option.

Given these tables :-

Level1Table

L1key L1Code

1           A
2    B
3    C

CodesTable

CTKey     CTType     CTCode

1    X        CA1
2    X        CA2
3    X        CA3
4    Y        CB1
5    Y        CB2
6    Y        CB3

LinkTable

Key    L1Code XKey    YKey
1    A    CA1    CB1
1    A    CA1    CB2
1    A    CA1    CB3
1    A    CA2    CB1
1    C    CA1    CB1
1    C    CA1    CB2

Is there a single query which will return all combinations of

L1Code XKey YKey

Which don't exist in table LinkTable ? Seems a bit like Sudoku to me :-)

Any help gratefully received,

--
Jim
a Yorkshire polymoth

14 Jul 2006 7:02 AM

Chris Lim

Jim Lawton wrote:
Show quote

> I have to resolve this problem - I have various ways I can do it, but I
> don't know enough SQL to do it in a single query (I have to do that,
> because the solution is cross-platform, and has to wrk where stored
> procedures aren't an option.
>
> Given these tables :-
>
> Level1Table
>
> L1key L1Code
>
> 1           A
> 2    B
> 3    C
>
>
>
> CodesTable
>
> CTKey     CTType     CTCode
>
> 1    X        CA1
> 2    X        CA2
> 3    X        CA3
> 4    Y        CB1
> 5    Y        CB2
> 6    Y        CB3
>
>
> LinkTable
>
> Key    L1Code XKey    YKey
> 1    A    CA1    CB1
> 1    A    CA1    CB2
> 1    A    CA1    CB3
> 1    A    CA2    CB1
> 1    C    CA1    CB1
> 1    C    CA1    CB2
>
>
> Is there a single query which will return all combinations of
>
> L1Code XKey YKey
>
> Which don't exist in table LinkTable ? Seems a bit like Sudoku to me :-)

This is untested, but something like this might work:

SELECT L1Code, XKey = ct1.CTCode, YKey = ct2.CTCode
FROM Level1Table l
INNER JOIN CodesTable ct1
    ON ct1.CTType = 'X'
INNER JOIN CodesTable ct2
    ON ct2.CTType = 'Y'
WHERE NOT EXISTS(SELECT *
                                 FROM LinkTable lt
                                 WHERE lt.L1Code = l.L1Code
                                 AND      lt.XKey = ct1.CTCode
                                 AND      lt.YKey = ct2.CTCode )

Chris

14 Jul 2006 7:06 AM

Uri Dimant

Jim
Why not just posting proper DDL+ sample data + expected result?

A guees

SELECT <> FROM CodesTable WHERE NOT EXISTS (SELECT * FROM LinkTable WHERE
CodesTable.pk= LinkTable.pk)

pk---Primary Key

Show quote

"Jim Lawton" <usenet1@jimlawton.TAKEOUTinfo> wrote in message
news:jueeb25hn89j3c84fkiu7auud232ljbpc6@4ax.com...
>
> I have to resolve this problem - I have various ways I can do it, but I
> don't know enough SQL to do it in a single query (I have to do that,
> because the solution is cross-platform, and has to wrk where stored
> procedures aren't an option.
>
> Given these tables :-
>
> Level1Table
>
> L1key L1Code
>
> 1           A
> 2 B
> 3 C
>
>
>
> CodesTable
>
> CTKey     CTType     CTCode
>
> 1 X CA1
> 2 X CA2
> 3 X CA3
> 4 Y CB1
> 5 Y CB2
> 6 Y CB3
>
>
> LinkTable
>
> Key L1Code XKey    YKey
> 1 A CA1 CB1
> 1 A CA1 CB2
> 1 A CA1 CB3
> 1 A CA2 CB1
> 1 C CA1 CB1
> 1 C CA1 CB2
>
>
> Is there a single query which will return all combinations of
>
> L1Code XKey YKey
>
> Which don't exist in table LinkTable ? Seems a bit like Sudoku to me :-)
>
> Any help gratefully received,
>
>
>
> --
> Jim
> a Yorkshire polymoth

14 Jul 2006 7:18 AM

Omnibuzz

select A.L1code, B.CTCode as XKey,C.CTCode as YKey from Level1Table A,
(select CTCode from CodesTable where CTType = 'x') B,
(select CTCode from CodesTable where CTType = 'y') C
where not exists (select 1 from LinkTable D where D.L1Code = A.L1Code
and D.XKey = B.CTCode and D.YKey = C.CTCode )

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

14 Jul 2006 7:33 AM

oj

Sure.

select lev1.*,x.CTCode [XKey],y.CTCode [YKey]
from Level1Table lev1
cross join (select CTCode from CodesTable where CTType='X')x
cross join (select CTCode from CodesTable where CTType='Y')y
where not exists(select *
from LinkTable l
where l.[key]=lev1.L1key and l.L1Code=lev1.L1Code and l.XKey=x.CTCode and
l.Ykey=y.CTCode)
order by lev1.L1key

--
-oj

Show quote

"Jim Lawton" <usenet1@jimlawton.TAKEOUTinfo> wrote in message
news:jueeb25hn89j3c84fkiu7auud232ljbpc6@4ax.com...
>
> I have to resolve this problem - I have various ways I can do it, but I
> don't know enough SQL to do it in a single query (I have to do that,
> because the solution is cross-platform, and has to wrk where stored
> procedures aren't an option.
>
> Given these tables :-
>
> Level1Table
>
> L1key L1Code
>
> 1           A
> 2 B
> 3 C
>
>
>
> CodesTable
>
> CTKey     CTType     CTCode
>
> 1 X CA1
> 2 X CA2
> 3 X CA3
> 4 Y CB1
> 5 Y CB2
> 6 Y CB3
>
>
> LinkTable
>
> Key L1Code XKey    YKey
> 1 A CA1 CB1
> 1 A CA1 CB2
> 1 A CA1 CB3
> 1 A CA2 CB1
> 1 C CA1 CB1
> 1 C CA1 CB2
>
>
> Is there a single query which will return all combinations of
>
> L1Code XKey YKey
>
> Which don't exist in table LinkTable ? Seems a bit like Sudoku to me :-)
>
> Any help gratefully received,
>
>
>
> --
> Jim
> a Yorkshire polymoth

14 Jul 2006 4:53 PM

Jim Lawton

On Fri, 14 Jul 2006 06:46:28 GMT, Jim Lawton
<usenet1@jimlawton.TAKEOUTinfo> wrote:

Thanks for all your replies, which enabled me to come up with a viable
solution, your help much appreciated...

--
Jim
a Yorkshire polymoth