Jump to content


Photo

Sub Query?


  • Please log in to reply
No replies to this topic

#1 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 07 December 2005 - 05:48 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users