sql

Can MySQL / SQL's short hand of "Using" be used without saying "Inner Join" ?

The following 2 statements are to join using gifts.giftID = sentgifts.giftID: mysql> select * from gifts, sentgifts using (giftID); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'using (giftID)' at line 1 and the second one: m...

Why is the ( ) mandatory in the SQL statement "select * from gifts INNER JOIN sentgifts using (giftID);" ?

Why is the ( ) mandatory in the SQL statement select * from gifts INNER JOIN sentgifts using (giftID); ? The ( ) usually is for specifying grouping of something. But in this case, are we supposed to be able to use 2 or more field names...? in the example above, it can be all clear that it is 1 field, is it just that the parser i...

How to model a mutually exclusive relationship in SQL Server

Hi, I have to add functionality to an existing application and I've run into a data situation that I'm not sure how to model. I am being restricted to the creation of new tables and code. If I need to alter the existing structure I think my client may reject the proposal.. although if its the only way to get it right this is what I will...

SQL query to return data from two separate rows in a table joined to a master table

I have a TWO tables of data with following fields table1=(ITTAG,ITCODE,ITDESC,SUPcode) table2=(ACCODE,ACNAME,ROUTE,SALMAN) This is my customer master table that contains my customer data such as customer code, customer name and so on... Every Route has a supervisor (table1=supcode) and I need to know the supervisor name in my t...

In SQL, why is "Distinct" not used in a subquery, when looking for some items "not showing up" in the other table?

Usually when looking for some items not showing up in the other table, we can use: select * from gifts where giftID not in (select giftID from sentgifts); or select * from gifts where giftID not in (select distinct giftID from sentgifts); the second line is with "distinct" added, so that the resulting table is smaller, and probably...

Are there stored procedure writing guidelines?

There are a lot of information about how to write an stored procedure from a practical view, in a very database server language dependent way, but since I and the team that I work everyday already know that, I was looking for a more theoretical discussion about how to do this, in some way language or database server independent. I want ...

Parallelizing L2S Entity Retrieval

Assuming a typical domain entity approach with SQL Server and a dbml/L2S DAL with a logic layer on top of that: In situations where lazy loading is not an option, I have settled on a convention where getting a list of entities does not also get each item's child entities (no loading), but getting a single entity does (eager loading). ...

After writing SQL statements in MySQL, how to measure the speed / performance of them?

I saw something from an "execution plan" article: 10 rows fetched in 0.0003s (0.7344s) (the link: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ ) How come there are 2 durations shown? What if I don't have large data set yet. For example, if I have only 20, 50, or even just 100 records, I can...

SQL Server 2005 I get "The database is not accessible"

I have a computer working as a server with many SQL Server 2005 databases in it since two years ago. Today, I don't know why, when I try to connect locally to any of the databases it gives me that error. It fails even if I'm logged in using Windows Authentication or as 'sa' user. It just wouldn't let me access the databases' info. Coul...

What's wrong with this UPDATE FROM using a case statement?

update p set p.storePrice = CASE WHEN p.costPrice BETWEEN 0.00 AND 1.00 THEN p.costPrice * 1.0 CASE WHEN p.costPrice BETWEEN 0.00 AND 1.00 THEN p.costPrice * 1.0 ELSE p.msrpPrice END FROM product p WHERE p.type = 1 The error says: Msg 156, Level 15, State 1...

In SQL, what does Group By mean without Count(*), or Sum(), Max(), avg(), ..., and what are some uses of it?

In SQL, if we use Group By without Count(*) or Sum(), etc, then the result is as follows: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | ...

In SQL, why is "select *, count(*) from sentGifts group by whenSent;" ok, but when "*" and "count(*)" is switched position, then it gives an error?

In SQL, using the table: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | 1 | 2010-04-24 | 123 | 456 | 2010-04-24 01:52:20 | 10...

Select all rows where a varchar column converts to a decimal

I have a varchar column that has generally has a decimal value, but some times there is some garbage text characters in that field. Is it possible to filter in the WHERE clause for rows that sucessfully convert to a decimal value? I am using sql-server 2005 ...

In SQL, a Join is actually an Intersection? And it is also a linkage or a "Sideway Union"?

I always thought of a Join in SQL as some kind of linkage between two tables. For example, select e.name, d.name from employees e, departments d where employees.deptID = departments.deptID In this case, it is linking two tables, to show each employee with a department name instead of a department ID. And kind of like a "linkage"...

In SQL, we can use "Union" to merge two tables. What are different ways to do "Intersection"?

In SQL, there is an operator to "Union" two tables. In an interview, I was told that, say one table has just 1 field with 1, 2, 7, 8 in it, and another table also has just 1 field with 2, and 7 in it, how do I get the intersection. I was stunned at first, because I never saw it that way. Later on, I found that it is actually a "Join" ...

Help with MySQL Query?

I have two tables rooms and users. I want to get only rooms.room_id, users.user_name with user_id = 1. I can get the result of all users with following sql... select rooms.room_id, rooms.user_id, users.user_name from rooms LEFT JOIN users ON rooms.user_id = users.user_id When I do like this to filter the result with...

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joinly?

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joining by: select * from t1, t2 where t1.ID = t2.ID; ? Is it just for style or to distinguish between outer join or are there other reasons for it? ...

In SQL, can we always write an inner join statement as a main query and subquery?

In SQL, can we always write an inner join statement as a main query and subquery? For example, select * from gifts g where g.giftID in (select giftID from sentGifts); can do a join and show the gifts sent in the sentGifts table, but it won't be able to show the sentTime because that is inside the subquery? ...

Syntax for combining joins in mysql

I seem to remember reading somewhere that there is a way to combine LEFT JOIN statements into a more simple query. I have the following query and was hoping someone could kindly take a look at it. SET @userId = 8; SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches FROM user_goal ug, user u, profile p LEFT JOIN user_block ub ON @user...

How to access referenced table from ASPX in-line code (datagridview control)

Hi, i am trying to bind data to a datagridview control on an ASPX webpage and am using something like this.. <asp:TemplateField HeaderText="MyField"> <ItemTemplate> <%# DataBinder.Eval(Container.DataItem, "MyField") %> </ItemTemplate> </asp:TemplateField> the problem i am having is that the data for the 'MyField' field...