Jump to content

Archived

This topic is now archived and is closed to further replies.

barkster

Sub Query?

Recommended Posts

I'm trying to do a count on a table and group by CustID then using the same table display information on two other tables. Table1 is CustGame and Table2 is Games. I want to get the count of Games by CustID in Table1 then display the CustGame along with the related game name, etc... I built in access using two queries but cannot use queries here so I don't know how else to go about it. I'm using Dreamweaver and thought I could do it there but can't figure it out. Here are the queries I built in Access if it helps. I tried creating a temp table but couldn't get that to work, if I could only run two queries to make one that would be great. Any suggestions on how I could do this, I want to use the results from Query1 in Query2?

 

Query1

SELECT Count(CustomerGames.CustGameID) AS CustGameCount, CustomerGames.CustID

FROM CustomerGames

GROUP BY CustomerGames.CustID;

 

Query2

SELECT CountGames.CustGameCount, CustomerGames.CustID, CustomerGames.GameID, CustomerGames.Condition, Games.Name, Games.Platform

FROM CountGames INNER JOIN (Games INNER JOIN CustomerGames ON Games.GameID = CustomerGames.GameID) ON CountGames.CustID = CustomerGames.CustID

WHERE (((Games.Name) Like "*ba*"));

 

I've even tried making a sub query but that didn't work for me.

 

Select

customergames.GameID,

customergames.condition,

ImportedGames.Name,

ImportedGames.Platform,

customer.Username,

(SELECT Count(customergames.CustGameID) FROM customergames WHERE customergames.CustID = customer.CustID GROUP BY customergames.CustGameID) AS CustGameCount

From

customer

Inner Join customergames ON customer.CustID = customergames.CustID

Inner Join ImportedGames ON customergames.GameID = ImportedGames.GameID

WHERE (((ImportedGames.Name) Like "*ba*"))

 

 

PS: Anyone know of a host that is using 5.0? Would love to use views and stored procedures. Thanks

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.