joins

Select all projects that have matching tags

I'm trying to find the most efficient way of dealing with this but I must tell you front-head I've made a mess of it. Looked around SO and found nothing of relevance so here it goes. How to select all projects that have similar tags to the desired project? Take this table for example: (sql code to recreate tables bellow) project 1 -...

The old IN vs. Exists vs. Left Join (Where ___ Is or Is Not Null); Performance

I have found my self in quite a pickle. I have tables of only one column (supression or inclusion lists) that are more or less varchar(25) but the thing is I won't have time to index them before using them in the main query and, depending how inportant it is, I won't know how many rows are in each table. The base table at the heart of al...

Condensing linq expressions into one expression for a simple blog system

I currently have many linq expressions nested within foreach loops ... kind of defeating the point of using linq! What I'm trying to achieve is a simple blog model (blog posts which have multiple tags and are associated with multiple categories... that's all). I'm looking for a way to condense all my linq expressions into a single expr...

how do I get this :through?

so I have these relationships: a location: has_many :services has_many :products, :through => :services a product: has_many :services has_many :locations, :through => :services has_many :add_ons and a service: belongs_to :product belongs_to :location has_many :service_add_ons has_many :add_ons, :through => :service_add_on...

SQL change query to show orphans

Give the query: SELECT tblProducts.ID, tblProducts.views, tblProducts.productName, tblProducts.isForSale, tblProducts.isLimitedStock, tblProducts.stockCount, tblProducts.description, tblProducts.weightKG, tblProducts.basePrice, tblProducts.dateCreated, tblProductCats.catName, (SELECT COUNT(*) FROM tblProductPrices WHERE product...

MySQL problem involving crazy multiple self-joins

As part of the process of replacing some old code that used an incredibly slow nested select, I've ended up with a query that looks like this: SELECT r3.r_id AS r3_id, r2.r_id AS r2_id, r1.r_id AS r1_id FROM table_r r3 LEFT JOIN ( table_r r2 INNER JOIN ( table_r r1 INNER JOIN table_d d ON r1.r_id = d.r_id ) ON r2.r_id = r1...

Making a more efficient join

Here's my query, it is fairly straightforward: SELECT INVOICE_ITEMS.II_IVNUM, INVOICE_ITEMS.IIQSHP FROM INVOICE_ITEMS LEFT JOIN INVOICES ON INVOICES.INNUM = INVOICE_ITEMS.II_INNUM WHERE INVOICES.IN_DATE BETWEEN '2010-08-29' AND '2010-08-30' ; I have very limited knowledge of SQL, but I'm trying to understand s...

Set operation on TSQL (SQL 2005/2008)

When a set is given say {1,2,3,4,5,6} The task is to separe pair of subsets {1,2}, {1,3}, {1,4}, {1,5}, {1,6}, {2,3}, {2,4}, {2,5}, {2,6}, {3,4}, {3,5}, {3,6}, {4,5}, {5,6} So when i have a table Table Element 1 2 3 4 5 6 What is the way to list out all possible pair of comma separated subset ? (Duplicates can be ignored (i.e) {1,...

Different database tables joining on single table

So imagine you have multiple tables in your database each with it's own structure and each with a PRIMARY KEY of it's own. Now you want to have a Favorites table so that users can add items as favorites. Since there are multiple tables the first thing that comes in mind is to create one Favorites table per table: Say you have a table ...

Easy method to remove unnecessary tables/dependencies from a large complex join.

I have a large complex set of joins in an sql statement (roughly getting 250 fields and using about 80 joins). I would like to split this large cumbersome query into some smaller ones, say returning only 10 fields each. However, the task of removing all of the joins (which are unnecessary for those 10 fields) is massively time consuming...

SQL show record when join has no records

SELECT tblProducts.productName, tblProducts.basePrice, tblProductOptions.optionDescription FROM tblProducts CROSS JOIN tblProductOptions WHERE (tblProducts.ID = 3) AND (tblProductOptions.ID = 5) If (tblProductOptions.ID = 5) then it works, there is an option with ID = 5. If it's (tblProductOption...

Rails query with attributes on two tables

How do I write a find(:all) query in rails (v2.3.5) that has parameters both in the model I am running the 'find' on, and in another table? e.g. I am trying to search for 'posts' by 'author' and 'tag'. Author is an attribute of post, whereas tag is associated with it through another table. I can get the tags by including ':joins =...

rails 3 activerecord - for :joins wrong sql is generated

i have the following 2 classes. class Customer < ActiveRecord::Base set_table_name "customer" set_primary_key "customerId" has_many :new_orders, :foreign_key => "customerid", :primary_key => "customerId", :class_name => "NewOrder" end class NewOrder < ActiveRecord::Base set_table_name "viewNewOrders" set_primary_key "orderid" bel...

Query works in PostgreSQL but fails with HSQL. How can I fix it?

Dear SQL gurus ;-) I have the following query (inherited from legacy) similar to SELECT bla FROM table WHERE some.id IN ( SELECT id FROM ( SELECT some FROM tag WHERE bla UNION SELECT some FROM dossierinfo WHERE bla ORDER BY tag LIMIT :limit OFFSET :offset ) AS aggregated WHERE dossier_type = ...

Is it possible to have multiple composite (aka "many-to-many", ManyToMany) joins that don't make the result set huge.

Situation: Table book is associated with one or more authors via the _author_book table. It is also associated with one or more genres via the _book_genre table. When selecting all the books, and all their genres, and all their authors, the number of rows returned is (assume each book has at least one genre and author): PROBLEM: book...

many to many select query

I'm trying to write code to pull a list of product items from a SQL Server database an display the results on a webpage. A requirement of the project is that a list of categories is displayed at the right hand side of the page as a list of checkboxes (all categories selected by default) and a user can uncheck categories and re-query th...

ActiveRecord find with include, conditions on the include that don't affect the parent

I have two models: class Parent < ActiveRecord::Base has_many :children end class Child < ActiveRecord::Base belongs_to :parent end I want to find all parents AND their children, with conditions on the children only. BUT if the parent has no children that match that criteria, I still want the parent. I tried this: Parent.all...

Grails relationships with static mapped tables

For the life of me I cannot seem to get relationships to work on mapped tables with Grails. I have two domains I am trying to join, Resources and Cassettes. A Resource can have many Cassettes. If i run the code below using scaffolding I get an error "Unknown column 'this_.cassette_id' in 'field list'". If i try to define the cassette_i...

SQL - Joining tables where one of the columns is a list

Hi Experts, I'm tryin to join two tables. The problem i'm having is that one of the columns i'm trying to join on is a list. So is it possible to join two tables using "IN" rather than "=". Along the lines of SELECT ID FROM tableA INNER JOIN tableB ON tableB.misc IN tableA.misc WHERE tableB.miscTitle = 'help me please' table...

two way joins mysql query

message table id user_id| message 1 1 | this is my cruel message 2 1 | this is my happy message 3 2 | this is happy messgae message_tags table id message_id| tags 1 2 | happy 2 3 | happy what i want to acess all the messages that have the the tag happy, how would construct...