TWiki> Main Web>SQLQueryInfo (28 Jul 2008, MattWalsh)EditAttach

Misc queries

Because MattWalsh still barely can remember to do queries, he keeps a list of ones he figures out for later.
  • Select e.CategoryID, d.eBayCatDesc from eBayCategory1 as e INNER JOIN eBayCatDesc as d where e.CategoryId = d.eBayCatID and e.CategoryName Like "%Books%";

Query of # of auctions per day not including administrators

SELECT Date(U.dAdded), count(Date(U.dAdded)) FROM UserSearch? U LEFT JOIN User UD on UD.nUserID = U.nUserID where UD.sPrivil NOT LIKE "%A%" group by Date(U.dAdded)

Number of queries not incl admins

SELECT * FROM UserSearch? US LEFT join User U on US.nUserID = U.nUserID where (US.nUserID = 0 OR sPrivil NOT LIKE "%A%")

Subquery for dealing with count

select * from (SELECT nFFItemID, count(nFFItemID) as mult_count FROM FFItem_Sale F group by nFFItemID) as temp where temp.mult_count > 1

Without a subquery, not sure how to select only rows with count > 1

-- MattWalsh - 02 Feb 2007

Edit | Attach | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | More topic actions
Topic revision: r4 - 28 Jul 2008 - 22:49:17 - MattWalsh
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback