Multiple references in a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple references in a query?

Author

Topic

BoilerUp218
Starting Member

2 Posts

Posted - 2010-06-02 : 12:59:12

Hi everyone,
I'm relatively new at the SQL thing, trying to teach myself.

I'm trying to write a query that works as follows:

Find a row in table A, compare it to a row in table B, then using some of the data in table B, find a different row back in table A...

For example, if table A is "shoe prices at different competitors" and table B is "type of shoe", it'd be like this

Find a Nike352 shoe selling at any competitor other than sports authority that is selling for less than any Nike at sports authority...

I hope that was clear. I have two ways I think I can do it, but i'll try with just the first:

Any help is appreciated!

d1
WHERE
((CC NOT IN 'XX'

And exists (Select 1 from DOM_C_MARKETS
WHERE ID like 'ABCDE__'
AND (d1.OWFA<OCC
OR d1.OWFA>DCC)
AND substr(d1.MG,1,1)=substr(ID,7,1))

and exists (select 1 from DOM_F_MARKET d2
Where CC IN 'XX'
and d1.occ=occ
and d1.dcc=dcc
and d1.rtfa<rtfa)))

And exists (select 1 from DOM_C_MARKETS
WHERE ID like 'ABCDE__'
and substr(ID,6,1)=substr(d2.fcc,1,1))

But the d2 reference does not work...

BoilerUp218
Starting Member

2 Posts

Posted - 2010-06-02 : 15:40:32

I hope that was clear enough

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-02 : 16:55:42

yup...clear as mud


-- Find a Nike352 shoe selling at any competitor 
-- other than sports authority 
-- that is selling for less than any Nike (and that would be MAX(Price) right?) 
-- at sports authority...

CREATE TABLE #myTable99(
	Retailer	varchar(50)
,	Shoe		varchar(50)
,	Price		money
)
GO

INSERT #myTable99 (Retailer, Shoe, Price)
SELECT 'Sport Authority', 'Nike352', 150.00 UNION ALL
SELECT 'Sport Authority', 'Nike456', 250.00 UNION ALL
SELECT 'Sport Authority', 'Nike789', 50.00 UNION ALL
SELECT 'Foot Locker'	, 'Nike352', 150.00 UNION ALL
SELECT 'Foot Locker'	, 'Nike456', 250.00 UNION ALL
SELECT 'Foot Locker'	, 'Nike789', 350.00 UNION ALL
SELECT 'Foot Action'	, 'Nike352', 149.00 UNION ALL
SELECT 'Foot Action'	, 'Nike456', 249.00 UNION ALL
SELECT 'Foot Action'	, 'Nike789', 349.00 UNION ALL
SELECT 'Marshals'		, 'Nike352', 49.00  UNION ALL
SELECT 'Sear'			, 'Nike352', 1.00   UNION ALL
SELECT 'LLBean'			, 'Nike352', 249.00 UNION ALL
SELECT 'Land Fill'		, 'Nike352', .01
GO

SELECT * FROM #myTable99
WHERE Retailer <> 'Sport Authority'
AND Shoe = 'Nike352'
AND Price < (SELECT MAX(Price) FROM #myTable99
				WHERE Retailer = 'Sport Authority')
GO


DROP TABLE #myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources