SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value FROM prodtree_element pe LEFT JOIN prodtree_link pl ON pe.prodtree_element_id = pl.to_prodtree_node_id LEFT JOIN line li ON pe.line_code = li.line_code INNER JOIN attribute_values av ON av.attribute_definition_id = #st...
Ok, so we have a bunch of tables that are named like this: training_data_001 training_data_002 training_data_003 training_data_004 training_data_005 And then to find those we look at a field in another table, let's just call it master.training_type. Anyway, I was wondering if anyone knew of a way to do a weird table name based join w...
After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different. What is the difference and what should go in each? If I understand the theory correctly, the query optimizer should be able to use both interchangeably. ...
This is my first attempt at answering my own question, since someone may well run into this and so it might be of help. Using Firebird, I want to combine the results of two queries using UNION ALL, then sort the resulting output on a given column. Something like: (select C1, C2, C3 from T1) union all (select C1, C2, C3 from T2) order b...
I've been looking at NHibernate recently and trying to learn the ins and outs. One thing I did notice in a few articles was the use of BuildSchema to create a testable database. The particular example used SqlLite as the database. Can BuildSchema be trusted to create your database or is it still best to create your own Database Schema i...
I have the following attributes in my DB. statistic id, devicename, value, timestamp. For a given statistic, I want to find the 2 most recent timestamps and corresopnding values for a every unique device. I am trying stuff like Trial 1) select statistic, devicename, value, timestamp from X_STATSVALUE where statistic=19 order by...
Problem: a table of coordinate lat/lngs. Two rows can potentially have the same coordinate. We want a query that returns a set of rows with unique coordinates (within the returned set). Note that distinct is not usable because I need to return the id column which is, by definition, distinct. This sort of works (@maxcount is the number of...
Can anyone provide (or point me to) example sql code for logging exceptions using Enterprise Library Exception Handling Application Block (EHAB)? ie I'd like standard CREATE TABLE and CREATE PROCEDURE statements for the structures and procs to store logged exceptions in a SQL Server database. Also, can anyone describe how the app block ...
It seems to me there are other functional dependencies you may wish to declare (other than superkeys of course), but SQL provides no easy way to do so. Why is this? ...
I'm porting a process which creates a MASSIVE CROSS JOIN of two tables. The resulting table contains 15m records (looks like the process makes a 30m cross join with a 2600 row table and a 12000 row table and then does some grouping which must split it in half). The rows are relatively narrow - just 6 columns. It's been running for 5 h...
Sorry about the vague subject but I couldn't think what to put. Here's my problem, I'm doing a query on a table that returns me a count of items related to a day. I want to make sure that if I do a query on the DB, I always get a set number of rows. For example, imagine I have the following table that contains a log of when people log...
HI, I have a large table from which i can query to get the following table type no of times type occurs 101 450 102 562 103 245 also i can get another table code no of times code occurs 0 1222 1 750 2 355 but now i want to write a query which ...
i have d1,d45,d79,33,d100 i want to sort these variables in ascending order from my table so any can help me out what is the query i have to go for this on order to get the output as d1 d33 d45 d79 d100 ...
Hello, I have the following code: http://www.nomorepasting.com/getpaste.php?pasteid=22615 Which is called by the javascript mentioned in this question: My problem is that I do not seem to be able to pass $query, as in nothing seemingly happens when I call this file by itself. I am unsure what the best way to control the flow of info...
A few years ago I worked on a system where a numeric primary key was stored in a [SQL Server] varchar column, so I quickly came unstuck when querying with a BETWEEN operator: SELECT ID FROM MyTable WHERE ID BETWEEN 100 AND 110; Results: 100 102 103 109 110 11 This was simply bad design. However, I'm working on an 3rd-party ERP syst...
MS SQL Server 2000 I have a column in Table A called Name. I wish to sort the Name field. Many but not all of the records for Name start will KL and are followed by a number (KL 1234, KL 2, KL 323, etc). Table A Name Able Bravo KL 2 KL 323 KL 1234 Zebra If I use Select Name from A Order by Name I get Able Bravo KL 1234 KL 2 KL ...
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".): SELECT A.identifier , A.name , TO_NUMBER(DECODE( A.month_no , 1, 200803 , 2, 200804 , 3, 200805 , 4, 20...
I have following situation. A main table and many other tables linked together with foreign keys. Now when I would like to delete a row in the main table a ConstraintsViolation will occur, which is intended and good. Now I want to be able to check if the ConstraintsViolation will occur before I trigger the delete row event. Is this po...
We are designing a new database and I would like some input in where to put stuff like default values. There are 3 scenarios: 1: New values, created_date field. Should the column have a default value when you do an insert? 2: Updated values, updated_date fiels. I've been thinkin about implementing a trigger that sets this to getdate(...
Hi Guys, Is it possible to connect to my remote database hosted at godaddy from locally from my PC? And if yes how? I tried the connection string provided to me but it didn't work. Thanks ...