Multiple Table Query Help Please

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

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Table Query Help Please

Author

Topic

puush
Starting Member

14 Posts

Posted - 2009-04-07 : 01:12:37

Hello,

I have a script that pulls data from 4 tables and I want to pull data from one more table with this script. The fifth table I want to pull from is called "EFT_TRANS" and I want to query data from the "Amount" Field. The linking field in the "EFT_TRANS" table is "Player_ID", I think. This data needs to be in a fifth column named "PBT Amount" in the query results.

I had some help writing this original script below and I know what it does but I don't totally understand all of the functions like the (sub select), Group By and Count-Sum functions. Any help would be greatly appreciated. The original script is below.

____________________________________________________

/* This script selects by given date range, a players Player_ID, Firstname, Lastname, address, city,
state, zip, Total Number of trips and total coin in. */

SELECT P.player_ID, p.FirstName, p.LastName, a.Address1A, a.Address1b, a.City1, a.State1, a.Zip1,
x.stattype, x.NumTrips, X.cashin

FROM CDS_PLAYER p, cds_account a,
(select st.meta_id as player_ID, sd.stattype, sum(sd.CashIn)as cashin, count(st.stattrip_id) as NumTrips

FROM CDS_STATTRIP st, CDS_StattripDetail sd

WHERE st.idtype = 'p'

AND st.StartGamingDate between '02/01/2009' and '02/28/2009' /* Enter Gaming Date range here */

AND st.stattrip_id = sd.stattrip_id

GROUP BY st.meta_id, sd.stattype)x

WHERE x.player_id = p.player_id

AND p.account_id = a.account_id

ORDER BY p.player_id, stattype

----------------------------------------------------------
Results

player_ID,FirstName,LastName,Address1A,Address1b,City1,State1,Zip1,stattype,NumTrips,cashin,AvgPerTrip
12,CINDY,MIL,P.O. BOX 8,,WILLITS,CA,95490,SLOT,7,$198.50 ,$28.36
13,EARNEST,POTT,50 SOUTH MAIN STREET #49,,WILLITS,CA,95490,SLOT,2,"$1,400.36 ",$700.18
14,THELMA,DD,18 LOCUST STREET #8,,WILLITS,CA,95490,SLOT,17,"$10,601.89 ",$623.64
15,RAELENE,DOERO,10 BAECHTEL RD,,WILLITS,CA,95490,SLOT,2,$382.89 ,$191.45
17,ROBERT,SHOAW,1 OSPREY COURT,,FORT

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-07 : 01:21:21

try like this
SELECT P.player_ID, p.FirstName, p.LastName, a.Address1A, a.Address1b, a.City1, a.State1, a.Zip1,
x.stattype, x.NumTrips, X.cashin,e.amount AS avgpertripFROM CDS_PLAYER p, cds_account a,
(select st.meta_id as player_ID, sd.stattype, sum(sd.CashIn)as cashin, count(st.stattrip_id) as NumTrips
FROM CDS_STATTRIP st, CDS_StattripDetail sd
WHERE st.idtype = 'p'
AND st.StartGamingDate between '02/01/2009' and '02/28/2009' /* Enter Gaming Date range here */
AND st.stattrip_id = sd.stattrip_id
GROUP BY st.meta_id, sd.stattype)x
INNER JOIN
(select player_id,avg(amount) as amount from EFT_TRANS group by player_id) e ON e.player_id = p.playerid
WHERE x.player_id = p.player_id
AND p.account_id = a.account_id
ORDER BY p.player_id, stattype

puush
Starting Member

14 Posts

Posted - 2009-04-08 : 02:50:07

hello,

Thanks for helping me man. I got the error below when i run this script in sql 200 query analyzer. i don't know what it means. Please assist. thanks.

--------------------------------------------------------------------
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'p' does not match with a table name or alias name used in the query.

---------------------------------------------------------------------
SELECT P.player_ID, p.FirstName, p.LastName, a.Address1A, a.Address1b, a.City1, a.State1, a.Zip1,
x.stattype, x.NumTrips, X.cashin,e.amount AS avgpertrip

FROM CDS_PLAYER p, cds_account a,
(select st.meta_id as player_ID, sd.stattype, sum(sd.CashIn)as cashin, count(st.stattrip_id) as NumTrips

FROM CDS_STATTRIP st, CDS_StattripDetail sd
WHERE st.idtype = 'p'
AND st.StartGamingDate between '02/01/2009' and '02/28/2009' /* Enter Gaming Date range here */
AND st.stattrip_id = sd.stattrip_id

GROUP BY st.meta_id, sd.stattype)x

INNER JOIN
(select player_id,avg(amount) as amount from EFT_TRANS group by player_id) e ON e.player_id = p.playerid

WHERE x.player_id = p.player_id
AND p.account_id = a.account_id

ORDER BY p.player_id, stattype

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

puush
Starting Member

14 Posts

Posted - 2009-04-15 : 00:07:36

I figured it out with help from other fellows.

The original query looked like it was built for MySQL. It lists the tables in the from clause seperated by comas and then joins in the where clause. That's not necessarily going to work with MS SQL. I instead used inner joins in the from clause for the first few tables rather than the lists.

SELECT
P.player_ID, p.FirstName, p.LastName, a.Address1A, a.Address1b,
a.City1, a.State1, a.Zip1, x.stattype, x.NumTrips, X.cashin,
e.amount AS avgpertrip
FROM
CDS_PLAYER p
INNER JOIN cds_account a on p.account_id = a.account_id
INNER JOIN (
select
st.meta_id as player_ID, sd.stattype,
sum(sd.CashIn)as cashin,
count(st.stattrip_id) as NumTrips
FROM CDS_STATTRIP st, CDS_StattripDetail sd
WHERE
st.idtype = 'p'
AND st.StartGamingDate between '02/01/2009' and '02/28/2009' /* Enter Gaming Date range here */
AND st.stattrip_id = sd.stattrip_id
GROUP BY st.meta_id, sd.stattype
) x ON x.player_id = p.player_id
INNER JOIN (
select player_id,avg(amount) as amount
from EFT_TRANS
group by player_id
) e ON e.player_id = p.playerid
ORDER BY p.player_id, stattype

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources