By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

Need help with SQL query

P: n/a
I need a help with the following problem:

There's 2 tables tbla(primary, foreign, data) and tblb(primary, order)
with data like this:

tbla
'company 1', 1000, 'New York'
'company 1', 1001, 'Boston'
'company 2', 1000, 'Atlanta'
'company 2', 1002, 'Los Angeles'
'company 3', 1001, 'Seattle'
'company 3', 1002, 'Orlando'
'company 4', 1002, 'Denver'

tblb
1000, 97
1001, 98

Resulting table should have only one of each company WHERE
tbla.foreign = tblb.primary ORDER BY tblb.order -- like this:
'company 1', 1000, 'New York', 97
'company 2', 1000, 'Atlanta', 97
'company 3', 1001, 'Seattle', 98

I desperately need any advice.

--
Michael

Feb 19 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Feb 19, 10:18 am, "regul8or" <regul...@gmail.comwrote:
I need a help with the following problem:

There's 2 tables tbla(primary, foreign, data) and tblb(primary, order)
with data like this:

tbla
'company 1', 1000, 'New York'
'company 1', 1001, 'Boston'
'company 2', 1000, 'Atlanta'
'company 2', 1002, 'Los Angeles'
'company 3', 1001, 'Seattle'
'company 3', 1002, 'Orlando'
'company 4', 1002, 'Denver'

tblb
1000, 97
1001, 98

Resulting table should have only one of each company WHERE
tbla.foreign = tblb.primary ORDER BY tblb.order -- like this:
'company 1', 1000, 'New York', 97
'company 2', 1000, 'Atlanta', 97
'company 3', 1001, 'Seattle', 98

I desperately need any advice.

--
Michael
Hi, Michael,

Give this a try:

WITH
TBLA
(CONAME, PKEY, CITY)
AS
(VALUES
('company 1', 1000, 'New York'),
('company 1', 1001, 'Boston'),
('company 2', 1000, 'Atlanta'),
('company 2', 1002, 'Los Angeles'),
('company 3', 1001, 'Seattle'),
('company 3', 1002, 'Orlando'),
('company 4', 1002, 'Denver')),
TBLB
(FKEY, QTY)
AS
(VALUES
(1000, 97),
(1001, 98))
SELECT
CONAME,
PKEY,
CITY,
QTY
FROM
(
SELECT
A.CONAME,
A.PKEY,
A.CITY,
B.QTY,
ROWNUMBER() OVER (PARTITION BY A.CONAME) RN
FROM
TBLA A
JOIN
TBLB B
ON
B.FKEY = A.PKEY
) ILV
WHERE
ILV.RN = 1;

Note that you will not need the WITH TBLA VALUES...TBLB VALUES stuff--
I used those CTE's since I don't have access to your tables :-)

HTH,

--Jeff

Feb 19 '07 #2

P: n/a
SELECT primary
, foreign
, data
, order
FROM (SELECT a.*, b.order
, ROWNUMBER() OVER(PARTITON BY a.primary
ORDER BY b.order) rn
FROM tbla a
INNER JOIN
tblb b
ON a.foreign = b.primary
) R
WHERE rn = 1

Feb 19 '07 #3

P: n/a
Thanks guys, that's great!

(Leaving to blow the dust from Ye Olde SQL Book of Magic)

--
Michael

Feb 19 '07 #4

P: n/a
On 19 Feb 2007 10:18:15 -0800, "regul8or" <re******@gmail.comwrote:
>I need a help with the following problem:

There's 2 tables tbla(primary, foreign, data) and tblb(primary, order)
with data like this:

tbla
'company 1', 1000, 'New York'
'company 1', 1001, 'Boston'
'company 2', 1000, 'Atlanta'
'company 2', 1002, 'Los Angeles'
'company 3', 1001, 'Seattle'
'company 3', 1002, 'Orlando'
'company 4', 1002, 'Denver'

tblb
1000, 97
1001, 98

Resulting table should have only one of each company WHERE
tbla.foreign = tblb.primary ORDER BY tblb.order -- like this:
'company 1', 1000, 'New York', 97
'company 2', 1000, 'Atlanta', 97
'company 3', 1001, 'Seattle', 98

I desperately need any advice.

Just thought i'd join the party.

SELECT
Primary,
Foreign,
Data,
Order
FROM
(
SELECT
a1.Primary Primary,
a1.Foreign Foreign,
a1.Data Data,
b1.Order Order,
(
SELECT
MIN(Order)
FROM
SESSION.tbla a2,
SESSION.tblb b2
WHERE
b2.Primary = a2.Foreign
AND a2.Primary = a1.Primary
) The_Min
FROM
SESSION.tbla a1,
SESSION.tblb b1
WHERE
a1.Foreign = b1.Primary
) c
WHERE
Order = The_Min
ORDER BY
Order
B.
Feb 20 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.