join

*= in Sybase SQL

I'm maintaining some code that uses a *= operator in a query to a Sybase database and I can't find documentation on it. Does anyone know what *= does? I assume that it is some sort of a join. select * from a, b where a.id *= b.id I can't figure out how this is different from: select * from a, b where a.id = b.id ...

Explicit vs implicit SQL joins

Is there any efficiency difference in an explicit vs implicit inner join? For example: select * from table a inner join table b on a.id = b.id; vs. select a.*, b.* from table a, table b where a.id = b.id; ...

Finding missing emails in SQL Server

I am trying to do something I've done a million times and it's not working, can anyone tell me why? I have a table for people who sent in resumes, and it has their email address in it... I want to find out if any of these people have NOT signed up on the web site. The aspnet_Membership table has all the people who ARE signed up on the ...

Ambiguity in Left joins (oracle only?)

My boss found a bug in a query I created, and I don't understand the reasoning behind the bug, although the query results prove he's correct. Here's the query (simplified version) before the fix: select PTNO,PTNM,CATCD from PARTS left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); and here it is after the fix: select PTNO,PTNM,P...

Multijoin queries in Django

What's the best and/or fastest method of doing multijoin queries in Django using the ORM and QuerySet API? ...

Is it better to join two fields together, or to compare them each to the same constant?

For example which is better: select * from t1, t2 where t1.country='US' and t2.country=t1.country and t1.id=t2.id or select * from t1, t2 where t1.country'US' and t2.country='US' and t1.id=t2.id better as in less work for the database, faster results. Sybase, and there's an index on both tables of country+id ...

Linq to Entity with multiple left outer joins

Hi, I am trying to understand left outer joins in LINQ to Entity. For example I have the following 3 tables: Company, CompanyProduct, Product The CompanyProduct is linked to its two parent tables, Company and Product. I want to return all of the Company records and the associated CompanyProduct whether the CompanyProduct exists or no...

SQL Question - Possibly Cursor/Join related?

Here is my situation: Table one contains a set of data that uses an id for an unique identifier. This table has a one to many relationship with about 6 other tables such that. Given Table 1 with Id of 001: Table 2 might have 3 rows with foreign key: 001 Table 3 might have 12 rows with foreign key: 001 Table 4 might have 0 rows with fo...

Combine multiple results in a subquery into a single comma-separated value

Hi, I'm sorry if the question is long-winded and/or unclear. I will try and make it clearer with any feedback I get. I've got two tables: TableA ID, Name TableB ID, SomeColumn, TableA_ID (FK for TableA) The relationship is one row of TableA - many of TableB. Now, I want to see a result like this: ID Name SomeColumn 1....

Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL?

When I started writing database queries I didn't know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this: SELECT a.someRow, b.someRow FROM tableA AS a, tableB AS b WHERE a.ID=b.ID AND b.ID= $someVar Now that I know that this is the same as an INNER JOIN I find all these queries in my c...

Many to many table queries

I have a many to many index table, and I want to do an include/exclude type query on it. fid is really a integer index, but here as letters for easier understanding. Here's a sample table : table t eid | fid ----+---- 1 | A 1 | B 1 | C 2 | B 2 | C 3 | A 3 | C 4 | A 4 | B 5 | B Here are some sample queries I want...

Subqueries vs joins

I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like where id in (select id from ... ) The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected an improvement, but can anyone explain why it was so drastic? The columns used in the where clau...

How do I find records that are not joined?

I have two tables that are joined together. A has many B Normally you would do: select * from a,b where b.a_id = a.id To get all of the records from a that has a record in b. How do I get just the records in a that does not have anything in b? ...

Javascript String concatenation faster than this example?

Hello, I have to concatenate a bunch of Strings in Javascript and am searching for the fastest way to do so. Let's assume that the Javascript has to create a large XML-"file" that, naturally, consists of many small Strings. So I came up with: var sbuffer = []; for (var idx=0; idx<10000; idx=idx+1) { sbuffer.push(‘<xmlta...

Confused on count(*) and self joins

Hello I want to return all application dates for the current month and for the current year. This must be simple, however I can not figure it out. I know I have 2 dates for the current month and 90 dates for the current year. Right, Left, Outer, Inner I have tried them all, just throwing code at the wall trying to see what will stick ...

MySQL Query: LIMITing a JOIN

Say I have two tables I want to join. Categories: id name ---------- 1 Cars 2 Games 3 Pencils And items: id categoryid itemname --------------------------- 1 1 Ford 2 1 BMW 3 1 VW 4 2 Tetris 5 2 Pong 6 3 Foobar Pencil Factory I want a...

SQL query: Simulating an "AND" over several rows instead of sub-querying

Suppose I have a "tags" table with two columns: tagid and contentid. Each row represents a tag assigned to a piece of content. I want a query that will give me the contentid of every piece of content which is tagged with tagids 334, 338, and 342. The "easy" way to do this would be (pseudocode): select contentid from tags where tagid ...

When and why are database joins expensive?

I'm doing some research into databases and I'm looking at some limitations of relational DBs. I'm getting that joins of large tables is very expensive, but I'm not completely sure why. What does the DBMS need to do to execute a join operation, where is the bottleneck? How can denormalization help to overcome this expense? How do other ...

How to do a LINQ Outer Join of a SubTable?

I have a data tables with this type of setup. Table A AID AName --------------------- 1 A_Name1 2 A_Name2 3 A_Name3 Table B BID BName --------------------- 10 B_Name1 20 B_Name2 30 B_Name3 Table C AID BID --------------------- 1 10 2 10 2 20 2 30 3 20 3 ...

SQL to find duplicate entries (within a group)

SQL to find duplicate entries (within a group) I have a small problem and I'm not sure what would be the best way to fix it, as I only have limited access to the database (Oracle) itself. In our Table "EVENT" we have about 160k entries, each EVENT has a GROUPID and a normal entry has exactly 5 rows with the same GROUPID. Due to a bug we...