Join syntax help

[drs]

I am trying to figure out some sql syntax, and I could use some help. This
is my first atempt at joins, so bear with me.

I have a table (A) which looks like the following

ID Data Source
-------------------------
1 abcdef 100
2 abcdef 100
3 abcdef 200
4 abcdef 200
5 abcdef 200

A second table (B) which looks like the following

Key ID
------------------------
Key1 1
Key1 2
Key1 3
Key1 4
Key2 1
Key2 2
Essentially, A is a table of items, and B is a table of where those items
have been used (Key1 is like an invoice which has items 1-4 on it, Key2 is a
second invoice with 1 and 2.) Source, in table A, is like the item
supplier.

I would like to get a list of every invoice (Key) that has used a part (ID)
from a particular Source.

So, for example, I would like to query for source 100 and get back (Key1,
Key2) or query for source 200 and get back only Key1.

To this end, I tried

"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =
100)"

But I got an empty recordset, so something is amiss.

Any help is greatly appreciated.

Thanks,

-d

[David Portas]

Please post some code that will actually reproduce the problem. Your query
worked for me and here's the proof:

/* (My assumptions about your tables and keys) */
CREATE TABLE A (id INTEGER PRIMARY KEY, data VARCHAR(10), source INTEGER NOT
NULL)
CREATE TABLE B ([key] VARCHAR(10), id INTEGER NOT NULL REFERENCES A (id),
PRIMARY KEY ([key],id))

INSERT INTO A (id, data, source)
SELECT 1, 'abcdef', 100 UNION ALL
SELECT 2, 'abcdef', 100 UNION ALL
SELECT 3, 'abcdef', 200 UNION ALL
SELECT 4, 'abcdef', 200 UNION ALL
SELECT 5, 'abcdef', 200

INSERT INTO B ([key],id)
SELECT 'Key1', 1 UNION ALL
SELECT 'Key1', 2 UNION ALL
SELECT 'Key1', 3 UNION ALL
SELECT 'Key1', 4 UNION ALL
SELECT 'Key2', 1 UNION ALL
SELECT 'Key2', 2

SELECT DISTINCT B.[key]
FROM B JOIN A
ON B.id = A.id
WHERE A.source = 100

Result:

key
----------
Key1
Key2

(2 row(s) affected)

--
David Portas
SQL Server MVP
--

[drs]

"David Portas" <RE****************************@acm.org> wrote in message
news:Vd********************@giganews.com...

Please post some code that will actually reproduce the problem. Your query
worked for me and here's the proof:

Goodness, sorry to waste your time, and thanks for the help nonetheless. It
seems I was querying for nonexistent data.

-d