In postgres I am fairly sure you can do something like this SELECT authors.stage_name, count(select id from books where books.author_id = authors.id) FROM authors, books; Essentially, in this example I would like to return a list of authors and how many books each has written.... in the same query. Is this possible? I susp...
Lets say I have the following tables: Customers Products CustomerProducts Is there a way I can do a select from the Customers and Products tables, where the values are NOT in the map table? Basically I need a matched list of Customers and Products they do NOT own. Another twist: I need to pair one customer per product. So If 5 cus...
I'm sure this is a fairly trivial problem, but I'm not sure what to google to find the solution. I have a table that looks like this: CREATE TABLE IF NOT EXISTS `transactions` ( `name` text collate utf8_swedish_ci NOT NULL, `value` decimal(65,2) NOT NULL, `date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swe...
How to query the results of a stored procedure? Here's my example: sp_columsn 'tblSomeTableName' --// Returns a recordset select COLUMN_NAME from [sp_columns 'tblSomeTableName'] --// Raises an error saying "Invalid object name 'sp_columns 'tblSomeTableName''." ...
Hi, I'm trying to learn nested queries in mysql and I'm stuck while selecting all hotels which are 30 miles away from the city and have rooms that cost 150$ I can chose the rooms which are 30 miles away from the city and costs 150 with this query,but can't reach the hotels. (select id from Rooms where cost = 150 and id in (select...
Have a table users and there is a field invited_by_id showing user id of the person who invited this user. Need to make a MySQL query returning rows with all the fields from users plus a invites_count field showing how many people were invited by each user. Something like this: SELECT User.*, Count.count FROM users AS User, ...
Can I somehow join tables and avoid the usage of distinct in the following MySQL query. invited_by_id shows the user id of who invited this user. SELECT user1.id, count(distinct user2.id) AS theCount, count(distinct user3.id) AS theCount2 FROM users AS user1 LEFT OUTER JOIN users AS user2 ON user2.invited_by_id=user1.id LEFT...
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ...
I'm using .NET 4 and I'm just wondering if there is an optimized way of achieving the following. Public Function GetUserByOpenID(ByVal claimedidentifier As String) As User Implements IUserRepository.GetUserByOpenID Dim user = (From u In dc.Users Where u.ID = (From o In dc.OpenIDs ...
I'm looking to do a single query to update a database. Here is some pseudocode: UPDATE Table1 SET Table1.Value = (SELECT Value FROM Table2 WHERE Table2.Id==2) WHERE Table1.Id == 4 ...
Is it always a best practice to use - Select E.Id,D.DeptName from Employee E join Dept D on E.DeptId=D.Id instead of - Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Id Other than readability and reducing length of the query what are benefits of using aliases ? When I consulted with our Database ex...
I have the following tables: Club: Club_ID | Title | Created_Date | ... Club_Intesect: User_ID | Club_ID | Access I'm trying to select a variable number of clubs, and join the ID of the user with the highest access in that club. This person is considered the owner. So if Club 100 has Members A, B, C with access 3,4, and 5 ...