sql

Ambiguous column name

I have the following SQL and it throws the error Ambiguous column name 'id' select tbl_registration.*, tbl_ebp.name as ebp_name, tbl_Users.id as user_id, tbl_ebp.id as linked_ebp_id from tbl_registration left outer join tbl_ebp on tbl_ebp.id = tbl_registration.ebp_id left outer join tbl_users on tbl_registration.email = tbl_users.userna...

MySQL : How to get records are have non null values ?

I want to get all records in case the result_1 is not null like below : SELECT ID, Code, NULLIF(CompareWithField,2.25) as result_1 FROM `data` WHERE Indexed = 0 and code = 142 and 'result_1' is not null But instead, every time I run the query, I receive a result even if result_1 reports NULL. Any solution ?...

SQL - detecting loops in parent child relations

I have parent child data in excel which gets loaded into a 3rd party system running MS SQL server. The data represents a directed (hopefully) acyclic graph. 3rd party means I don't have a completely free hand in the schema. The excel data is a concatenation of other files and the possibility exists that in the cross-references between...

Can't set output of 'WITH XMLNAMESPACES...FOR XML PATH' to a variable?

I have a query like the following: ;WITH XMLNAMESPACES ( DEFAULT 'http://www.somewhere.com') SELECT ( 'SOMETHING' ) FOR XML PATH('RootNode'), TYPE Running this works fine. However, I run into troubles when I try to set the XML output to a variable like this: DECLARE @MYXML AS XML SELECT @MYXML = ( ;WITH XMLNAMESPACES ( DEFAULT 'http...

ms-access: best way to add trailing character to every record

i will be importing a table with data. one of the columns called CAR will look something like this: mercedes,ford,toyota for every record in the DB i will need to add a comma , to the end of the string like: mercedes,ford,toyota, since i will be programmatically importing the table, what is the best way to add this trailing comma t...

How can I do a self-join with count/'group by' comparisons?

Given the following table (how to format those correctly here?) primary secondary A a A b A b B a B a B b I'm trying to get comparitive group-by counts using a self join. Getting the following result set is easy: Primary Secondary Count A a 1 A...

Inline If Statements in SQL

I wish to do something like this: DECLARE @IgnoreNulls = 1; SELECT Col1, Col2 FROM tblSimpleTable IF @IgnoreNulls BEGIN WHERE Col2 IS NOT NULL END ORDER BY Col1 DESC; The idea is to, in a very PHP/ASP.NET-ish kinda way, only filter NULLs if the user wishes to. Is this possible in T-SQL? Or do we need one large IF block like so: IF...

Using YQL how can I select an item from a feed that contains a colon in its name?

I'm using YQL to parse some web feeds, this one in particular. SELECT * FROM rss WHERE url='http://www.arena.net/blog/feed' This query returns a bunch of fields, one of which looks like content:encoded How can I select that field to filter? I want to do something like this, SELECT title, link, pubDate, content:encoded FROM rss WHER...

Sql server indexes tips

Hi, I have been reading for the last two days about indexes and how to optimize a database. Despite I have a clear idea on Indexes i don't know yet how to practically optimize my database. Could you suggest any tutorial or technique to optimize a database. Please be specific as i have been reading lots of theory but no specific instr...

Reduce dynamic SQL using CASE to use "IN" or not

I am converting a stored procedure which I had previously written as a string then, using BIT parameters I decided whether to append certain WHERE/ON clauses This sp is passed a number of comma-separated strings and then some of the dynamic WHERE clauses are like: IF @pUse_Clause_A THEN SET @WhereClause = @WhereClause + ' AND [FIELD_A]...

SQL row value as column name

I'm pretty experienced in C#, but still mostly a beginner in SQL. We have an application in C#, using an MSSQL database. One part of our application is simply a list of pre-written queries as reports that the application shows by simply running the query and sticking the returned table into a gridView. The user is requesting a new report...

Using zero value in MySQL columns of type TIMESTAMP

I'm a little confused over the documentation of type TIMESTAMP in MySQL and whether the zero value is safe to use. The manual says: The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. ... which implies that '0000-00-00 00:00:00' is out of range and thus not valid. But, as far as I can see,...

MySQL COUNT with GROUP BY and zero entries

I have 2 tables: Table 1. options_ethnicity with the following entries: ethnicity_id ethnicity_name 1 White 2 Hispanic 3 African/American Table 2. inquiries with the following entries: inquiry_id ethnicity_id 1 1 2 1 3 1 4 2 5 2 I want to generate a table that shows the number of inquires by ethnicity. My que...

LINQ to SQL querying against a view to populate an object model

We have the following data model: CalendarAppointment 1 <------> * AppointmentRole * <--------> 1 Person (or Group) A calendar appointment can have multiple persons associated with it. Each person can be in a different role (attending, driving, picking up or dropping off). (A person can also be a member of a group and groups can be ...

SQL - Group By with Left Join

I have two tables. Table A has a list of employee names. Table B is a complex table with information about phone calls made by employees. My goal is to make a table with columns 'name' and 'callCount'. I am aiming to do this with a 'left join' and a 'group by', but I keep missing the employees that have made no calls. How can I just get...

Re-run the query of store the results in a table variable?

I am in a situation where I need to run multiple times the same queries. I have to look if one of the rows returned correspond to a specific value (query 1) otherwise, I have to return the first row of the result set (query 2). In SQL Server 2008, I am wondering what is best: running the query and storing the results in a table variable ...

Default value in sql database design

Is it always good practice to provide default values for integer-like fields? I use linq for database access. ...

Optimizing SQL query with checking special rules set

(First of all sorry for my English. I am not native speaker) I have a problem with selecting data. My problem is that I don't know how to optimize SELECT from table "component". As I understand – I cannot create any indexes which help Postgres make select based on set of rules from other table. I have following schema: Component can h...

make zero appear last in a list of ascending numbers

I'm using SQLite in an Android application. In all of my tables I have a default row with an index of 0 that holds default values for that table. In most situations the default number for each field is 0 and that is the best number to use. However, when I sort my data using an ORDER BY statement I want to have all of my zero value fields...

sql combining two queries and truncating date

I am trying to truncate dates to only get year/month as opposed to the form they are in which is year/month/day.time What I want to do is count all of the cars that sold each month and all of the suvs that sold each month, having something like: // counts cars select SellDate, count(*) from category where machineIdentification =...