join

Joining multiple (4) tables in MYSQL

I have four tables I want to join and get data from. The tables look something like... Employees (EmployeeID, GroupID[fk], EmployeeName, PhoneNum) Positions (PositionID, PositionName) EmployeePositions (EployeePositionID, EmployeeID[fk], PositionID[fk]) EmployeeGroup (GroupID, GroupName) [fk] = foreign key I want to create a query t...

what's the best way to design dynamic key-value pairs in mysql?

Hi, I need to attach unlimited attributes to a record in a table, and I've already designed a system using #3 below using mysql. Unfortunately I am finding that searching across a million records is getting slow. Is #2 a better approach, or is there a better way alltogether? Is this a case for using a view? I'd like to have my keys ...

SubSonic inner join mystery

I've have 2 tables CampaignCoverage and Coverage CampaignCoverage has a CoverageID column. Coverage has a column DateX. Basically I've not specified in the code that CampaignCoverage.CoverageID = Coverage.ID like you would to in SQL. How does this work at all????!!!! CampaignCoverageCollection campaignCoverages = new Select() ...

mysql query help - complicated join/order by scenario

Hi, I need to write a query and I'm not even sure where to begin on this one. I have a set of tables that I did not create and cannot change. member table +----+-------+ | id | class | +----+-------+ | 1 | 1 | | 2 | 2 | +----+-------+ member_data table +----+------------+-----------+ | id | first_name | last_name | +----+----...

MySQL query involving cross join, uniqueness, etc

Thankfully, I haven't had to work with particularly complex SQL queries before. Here's my goal. I have the table hams, which I would like to cross-join with the table eggs - that is, get all ham-egg combinations... to an extent. The eggs table also has an attribute how_cooked, which is defined as ENUM('over-easy','scrambled','poached')...

Rails nested form with has_many :through, how to edit attributes of join model?

How do you edit the attributes of a join model when using accepts_nested_attributes_for? I have 3 models: Topics and Articles joined by Linkers class Topic < ActiveRecord::Base has_many :linkers has_many :articles, :through => :linkers, :foreign_key => :article_id accepts_nested_attributes_for :articles end class Article < Active...

MySQL - Counting rows and left join problem

I have 2 tables, campaigns and campaign_codes: campaigns: id, partner_id, status campaign_codes: id, code, status I want to get a count of all campaign_codes for all campaigns WHERE campaign_codes.status equals 0 OR where there are no campaign_codes records for a campaign. I have the following SQL, but of course the WHERE statement e...

Self join of time series data

I need some help with what I think should be a fairly simple self join query. It just needs to combine the matching start time and end times from two records into one record Say I have the following in a table Time Event 08:00 Start 09:00 Stop 10:30 Start 10:45 Stop 11:00 Start 11:15 Start 12:00 Stop 12:30 Stop I want a view like t...

MySQL Filter results

Hey, I have a table in my SQL in the following structure called actions: +----+--------+------+---------+ | id | action | type | user_id | +----+--------+------+---------+ | 1 | 5 | 4 | 1 | | 2 | 6 | 4 | 1 | | 3 | 5 | 4 | 2 | | 4 | 0 | 0 | 2 | | 5 | 0 | 1 | ...

ActiveRecord find query with join only doesn't include data from joined table

I have two tables: keyword_reports and keywords (with relevant AR models). keyword_reports has a keyword_id column, which I'm using to join the keywords table like so: KeywordReport.find(:all, :joins => :keyword, :conditions => {:page_id => 10}) which correctly pulls back records for keyword_reports, but doesn't include the data from...

Fluent NHibernate - How to use an enumerator to identify subclass?

Hi, Im trying to map the following classes: public abstract class ScheduleType { public virtual int Id { get; set; } public virtual TypeDiscriminatorEnum Discriminator { get; set; } } public class DerivedScheduleType : ScehduleType { public virtual bool MyProperty { get; set; } } public class ScheduleTypeMap : ClassMap...

Howto get newest dataset with SQL join?

Hi, I have got the following join: SELECT l.cFirma AS Lieferant, SUM(la.fEKNetto) AS Verbindlichkeiten, l.kLieferant AS Lieferanten_ID, 100 - gk1.fFaktor * 100 AS Grundkondition, MAX(gk1.dDatum) AS Datum FROM tBestellung b, tArtikel a, tBestellpos p, tLieferant l, tLiefArtikel la, tGrundkondition gk1...

SQL INNer Join On Null Values Question

I have a Join SELECT * FROM Y INNER JOIN X ON ISNULL(X.QID, 0) = ISNULL(y.QID, 0) Isnull in a Join like this makes it slow. It's like having a conditional Join. Is there any work around to something like this? I have a lot of records where QID is Null Anyone have a work around that doesn't entail modifying the data ...

Separate table in case of surrogate key?

I have been reading the heated debates on composite vs surrogate keys on Stack Overflow and other websites, and even though puzzled about some of the arguments given, I feel I am aware of the pros and cons of each. I feel tempted to go for surrogate keys, but now I have another question. Let me explain my situation. I have a table consi...

SQL Server 2005 - Multiple Databases

We are using SQL Server 2005 at work and when development started it was decided that multiple databases would be created. For example, we have one database for Individual say dbIndividual and another one for Translation say dbLocale. So far this has simplified the backing up and potential restore greatly as we just need to backup what...

EntityFramework - LINQ Join help

Hello, I have following schema: Clients - ClientId Users - UserId Offices - OfficeId ClientOffices - ClientId, OfficeId UserOffices - UserId, OfficeId Bold entities are EntityFramework Entities. Now I need to write a function which accepts UserId and returns a list of Clients - who also belong to the offices to which user belongs. ...

Using random value as join condition

I am generating some test-data and use dbms_random. I encountered some strange behavior when using dbms_random in the condition of the JOIN, that I can not explain: ------------------------# test-data (ids 1 .. 3) With x As ( Select Rownum id From dual Connect By Rownum <= 3 ) ------------------------# end of test-data Select x.id, ...

Hibernate Criteria: Left Outer Join with restrictions on both tables

Hi all, I am doing a LEFT OUTER JOIN, but I am only able to apply Restrictions on the first table. Is there a way ti apply on the second table as well? Here is my code: Criteria criteria = this.crudService .initializeCriteria(Applicant.class).setFetchMode("products", FetchMode.JOIN);. This works (applicant has a...

SQL Server-- join tables and SUM columns for each distinct row

Hi all, Could someone double check my SQL statement for proper operation and general commonsense approach? Here's what's going on: I have a parent and child table with a one-to-many relationship, joined on a column named AccountNumberKey. The child table has numeric columns in it I need to sum up. The data is such that all child r...

sql - left join - count

suppose i have two tables. articles and comments. when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid) how do I do that? I can get it done, but I do not know if my way would be effici...