Hi everyone, I have 3 tables: Emails Foo EmailFoos (The many to many join table) Foo can be complete or not. I need to find the set of emails where the count of completed foos > 0 (and the inverse, but I can probably do that ;) I tried something like: SELECT e.id, e.address, count(l.foo_id) as foo_count FROM emails e LEFT ...
Suppose I have three tables: user, group and xref, a table that gives them many-to-many RI. I might want to see how groups each user belongs to: select user.user_id, user.user_name, count(*) as group_count from user inner join xref on user.user_id = xref.user_id inner join group on group.group_id = xref....
I'm looking for examples, tutorials, or just "this+this+this should work" for reading from and writing to a SQL server (2008) from a microcontroller such as the Arduino board. I've also looked at (and will probably go with) devices with the .Net Micro Framework such as the Fez Cobra. The Micro Framework does not include ADO. I'm sure t...
here is my current queries: 1 SELECT FilteredInvoice.accountidname, FilteredInvoice.createdon, FilteredInvoice.createdon AS sort_date, FilteredInvoice.duedate, FilteredInvoice.invoicenumber, FilteredInvoice.statecodename, FilteredInvoice.totalamount_base, CONVERT(datetime, NULL) A...
I have 3 tables structured like so: activity table: activity_id, user_id, type, date reviews table: review_id, activity_id, fishery_id, review, date updates table: update_id, activity_id, update, date I want to call the all the reviews and updates which are linked to a user by the activity table however this query retu...
I am trying to add foreign keys to my table but receiving this error. Error Code: 1005 Can't create table 'william.#sql-88c_3' (errno: 150) I have 3 tables. employee, client and Contract. employe [employee_no PK] , Client[customer_no PK] contract [contract_no PK] I want to have Foreign keys for contract as contract [contract_no PK, empl...
I started with this but is it the best way to perform the task? select reverse( substring(reverse(some_field), charindex('-', reverse(some_field)) + 1, len(some_field) - charindex('-', reverse(some_field)))) from SomeTable How does SQL Server treat the multiple calls to reverse(some_field)? Besides a ...
I do data conversions, and I am constantly connecting to a variety of different DBMS'. Certain DBMS' do not have JDBC drivers (MsAccess for example). Sun's JDBC-ODBC bridge driver was meant as a short term solution when JDBC drivers weren't widely available, and because of that, it is lacking functionality and is pretty buggy. I am to...
Say I have a table of apples (apple_id). And for each apple I have a history of its weight over time. How to structure it so that every time an apple is added, an associated table is created with its weight history? Having only one table of weight history for all apples (apple_id,age,weight) seems like a performance drain when looki...
Basically, I want to insert if a given entry (by its primary key id) doesn't exist, and otherwise update if it does. What might be the best way to do this? ...
Is there a php function to get regular expressions able to check if an input fits a certain MySQL data type? In example: $foo = get_regex_for_data_type("int(10) unsigned"); echo $foo; would return something like: /^[0-9]{1,10}$/ ...
I want to insert a row of data into a table with five columns (this table joins members and games); four of the five columns are known, while the fourth, rank, has to be dynamically calculated: wishlists(id (int, pk), memberid (int, FK), gameid(int, FK), rank (int), createdat(timestamp) ) INSERT INTO wishlists (memberid, gameid, rank) ...
We're thinking about adding a weekly summary table to our little data warehouse. We have a classic time dimension down to the daily level (Year/Month/Day) with the appropriate Week/Quarter/etc. columns. We'd like to have the time key in this new weekly summary table reference our time dimension. What's the best practice here—have the ti...
I'm trying to call a WCF Service from a SQL Stored Procedure written in C#. I saw various posts or questions on about the same topic : http://stackoverflow.com/questions/3502343/calling-a-wcf-service-from-sql-clr-stored-procedure http://stackoverflow.com/questions/751500/sql-clr-stored-procedure-and-web-service But there's something I ...
I don't know where error information goes when a trigger doesn't work correctly. My tool for writing triggers has been Oracle's Sql Developer tool, and my knowledge of how to debug it is pretty much nonexistent. What are some pointers for being able to find useful information about things happening "behind the scenes"? Also, are the...
I'm kind of new to writing sql and I have a question about joins. Here's an example select: select bb.name from big_box bb, middle_box mb, little_box lb where lb.color = 'green' and lb.parent_box = mb and mb.parent_box = bb; So let's say that I'm looking for the names of all the big boxes that have nested somewhere inside them a litt...
hello, i was trying to run this query: ( (SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?)) UNION (SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?)) ) LIMIT 5 but got an error because of the sur...
How would I use a IN table with like? So that I could use % in them? By in I mean: SELECT fields FROM table WHERE age = "50" AND name IN ("tim", "bob", "nancy", "john"); I already tried: SELECT fields FROM table WHERE age = "50" AND name LIKE ("2010-09-17%", "2010-09-16%") But it gave the error "Operand should...
Is it possible to have a statement like SELECT "Hello world" WHERE 1 = 1 in SQL? The main thing I want to know, is can I SELECT from nothing, ie not have a FROM clause. ...
I'm trying to count my comments and there replies but I can't seem to get it right. Here is my query so far. SELECT posts_comments.*, users.* (SELECT COUNT(*) FROM posts_comments WHERE parent_comment_id >= 1) FROM posts_comments LEFT JOIN users ON posts_comments.user_id = users.user_id WHERE post_id = '" . $post_id . "' AND parent_c...