RE: SQL Question

Thats close, but lets say there was a key in tableA "AKEY" and there
are 8
records in tableB that reference that key.

I'd like to be able to return x rows from tableB that match "AKEY", so
it
could be 2 it could be 4 or 6

I believe that's what that does. Did you try it?

Here it is, revamped for your column names:

SELECT B.*
FROM TABLEA A
JOIN TABLEB B ON B.FKEYB = A.KEYA


Or wait!!! Do you mean this: (?)

SELECT B.*
FROM TABLEA A
JOIN TABLEB B ON B.FKEYB = A.KEYA
WHERE A.KEYA = 'AKEY'


This will return only rows from TABLEB that are also represented in TABLEA.


To help my feeble brain understand, please explain how the output from this
differs from the desired output.


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
Money is better than poverty, if only for financial reasons.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Neill Harper
Sent: Wednesday, September 02, 2009 7:05 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Question

Thats close, but lets say there was a key in tableA "AKEY" and there
are 8
records in tableB that reference that key.

I'd like to be able to return x rows from tableB that match "AKEY", so
it
could be 2 it could be 4 or 6

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: 02 September 2009 23:44
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Question

Now I think about it, you might have wanted:
SELECT B.*
FROM TABLEA A
JOIN TABLEB B ON B.OTHERCOLUMN = A.KEYCOLUMN

For all six record in file A return x number of rows from file B

where

the
key relationship matches (preferably random not an absolute
requirement).

I couldn't do this with one statement and ended up running several

to

get
the result I wanted.

Do you mean something like:
SELECT KEYCOLUMN, COUNT(*)
FROM A
JOIN B ON OTHERCOLUMN = KEYCOLUMN
GROUP BY KEYCOLUMN

If you want a count from all records in A regardless whether B has a
match, change JOIN to LEFT OUTER JOIN.


Dennis Lovelady
--
"A lawyer is a learned gentleman who rescues your estate from your
enemies... and keeps it for himself."
-- Henry Broughman

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.