sql

How do I reference a table-valued function in a common table expression?

Note: This issue appears to be limited to SQL Server 2005 SP2 I have a common table expression that iterates over a series of dates. I need to reference each of these dates in a call to a table-valued function. Something like this below, which gives a syntax error around the second parameter of the call to GetMyData. If I use another va...

Is it possible to delete master AND detail records in one SQL statement?

Cascading deletes aren't setup on this particular database. Just wondering if there's a way to do it in one swipe that's more efficient than running two separate statements. ...

SQL: Alternative to "First" function?

I'm trying to write a query I don't want to have Cartesian products on. I was going to use the First function, because some Type_Codes have multiple descriptions, and I don't want to multiply my dollars. Select Sum(A.Dollar) as Dollars, A.Type_Code, First(B.Type_Description) as FstTypeDescr From Totals A, TypDesc B Where A.Type_C...

Selecting Time ranges from Date Time fields in Access

I have a table containing reports and the date/time they were created. I'd like to create a graph to tally the number of reports created during intervals of ten minutes between two time periods: 8:00AM-9:00AM and 1:00PM-2:00PM. Here's an example of a query I'd like to run: SELECT s.StudyStartDateTime AS "8:00 - 8:10", s.StudyStartDate...

SQL - counting WHERE AGGREGATE>1

Imagine I have a db table of Customers containing {id,username,firstname,lastname} If I want to find how many instances there are of different firstnames I can do: select firstname, count(*) from Customers group by 2 order by 1; firstname | count(*) ==================== bob | 1 jeff | 2 adam | 5 ...

MySQL newbie question

I have two tables: CREATE TABLE pagelets ( `netloc` INT(32) unsigned NOT NULL, `page_key` BIGINT(64) unsigned NOT NULL, `pagelet_serial` BIGINT(64) unsigned NOT NULL, --that this is unique `pagelet_shingle` BIGINT(64) unsigned NOT NULL, `delete_me` BOOLEAN NOT NULL, KEY ( `pagelet_shingle` ) ) ENGINE=MyISAM...

ORM Select n + 1 performance; join or no join

There are similar questions to this, but I don't think anyone has asked this particular question. Scenario: Customer - Order (where Order has a CustomerID) - OrderPart - Part I want a query that returns a customer with all its orders and each order with its parts. Now I have two main choices: Use a nested loop (which produces separ...

ms-access: instead of rowsource, running from query

i have a very complex query that is running from a listbox rowsource. i just do a listbox1.requery and it populates the listbox. instead of doing it this way, i would like to: i just want to save the query in the queries section call it from there. then i want to save the results of the query into a string then i want to feed the stri...

Primary key/foreign Key naming convention

In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys. There's basically two schools of thought in our group: 1) Primary Table (Employee) Primary Key is called ID Foreign table (Event) Foreign key is called EmployeeID 2) Primary Table (Employee) Primary Key is called EmployeeID Forei...

ms-access save query result in a string

I have a query saved in the queries section. I am running the query from VBA. Is it possible to save the results of this query to a string? ...

Need help adding a conditional COUNT() to a query already using JOINs

Here's what I need to fetch: - posts that have comments - number of comments per post - number of unread comments per post (expressed by a bool "read" column in the "comments" table) The last part is what I'm having trouble with. Here's my SQL so far: SELECT posts.id , posts.title , COUNT(comments.id) AS commentsCo...

SQL - Multiple criteria with a LEFT OUTER JOIN

I am trying to do an OUTER JOIN, with multiple join conditions. Here is my query (I will explain issue below): SELECT ad.*, cp.P_A, cp.P_B, cp.P_C INTO #AggData3 FROM #AggData2 ad LEFT OUTER JOIN #CompPriceTemp cp ON ad.PART=cp.Part_No and ad.[Month]=cp.[Month] and ad.[Year]=cp.[Year] GO For each record in #AggData2, which is avera...

Using a SQL Ranking Function with a derived column

I am trying to use a SQL Server 2008 Ranking Function on a query sorted by a derived column. Here's an example SELECT table.FirstName, table.LastName, CalculatedValue(table.number) As Points, ROW_NUMBER() OVER (ORDER BY points) AS 'Row Number' FROM table ORDER BY points I always get an error invalid column name "point...

Database/SQL: How to store longitude/latitude data?

Performance question ... I have a database of houses that have geolocation data (longitude & latitude). What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that I can perform a lot of queries where I'm returning all the home records that are between x1 and x2 latitu...

Which Database can i Safely use a GUID as Primary Key besides SQL Server?

The reason I want to use a Guid is because in the event that I have to split the database into I won't have primary keys that overlap on both databases. So if I use a Guid there won't be any overlapping. I also want to use the GUID in the url also, so the Guid will need to be Indexed. I will be using ASP.NET C# as my web server. ...

Generate graphical schema representation from CREATE TABLE SQL

Hi all, I have a .sql file containing many CREATE TABLE statements. There's no referential specification (MyISAM stuff) but I would enjoy to create different table boxes, and then compose the table relationships by hand. I am using a mac. Is there a free program to do this ? ...

How does one extract the definition of a view using standard SQL?

In trying to answer this question for myself I came across this nugget, after eventually adding "oracle" to my query terms: select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL; Which works, but is not portable. How do I do the same thing on MySQL? SQLite? Others? ...

Aggregate function...select bottom n%

Hi, Here's an easy query to find the average height of boys and girls from a table of all children in a class. SELECT AVG(table1.height) FROM table1 GROUP BY table.sex Now I say I want to find the value of the bottom 20% of heights from all boys in a class. How can I do this? So if the possible values are 1,2,3,4,5,6,7,8,9,10 th...

Select more tables after FROM statement?

Hello, this is part of a security audition, so there is no way to "change" the query. Basically, what I found is a flaw that allows statement manipulation, so basically it goes like: $query = "DELETE FROM `products` WHERE `products`.`whatever` = $variable"; This is PHP, so as far as I know there is no way to execute multiple queries....

Oracle: find index creation date from systables/information_schema?

Using Oracle, how can I find index names and creation dates from systables/information_schema? How can I reproduce, from systables/information_schema, the DDL that created the index, e.g., create index indexname on tablename(column_name [, column_name....]) [local]; ...