self-join

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 ...

self join query

Consider the following table: mysql> select * from phone_numbers; +-------------+------+-----------+ | number | type | person_id | +-------------+------+-----------+ | 17182225465 | home | 1 | | 19172225465 | cell | 1 | | 12129876543 | home | 2 | | 13049876543 | cell | 2 | | 15064223454 | home | ...

Get Common Rows Within The Same Table

I've had a bit of a search, but didn't find anything quite like what I'm trying to achieve. Basically, I'm trying to find a similarity between two users' voting habits. I have a table storing each individual vote made, which stores: voteID itemID (the item the vote is attached to) userID (the user who voted) direction (whethe...

how do I add a foreign key pointing to the same table using myphpadmin?

I have an existing InnoDB table which already has foreign keys pointing to different tables. But when I try to create a foreign key pointing to the Primary index, I get an error (check data type). The table is User with User_Id as the Primary. I want a foreign key Manager_ID which is a FK to User_Id. Both of INT Both of Length 10 Uns...

MYSQL: Complex Query Question

Hello all, I am working with polls: Each poll has many options and users can vote in polls once. Thus I have a 'votes' table with the following fields: id (id of the vote) option_id (id of the poll option chosen) user_id (id of the user) poll_id (id of the poll) So here is what I'm trying to do: Given an array of poll_ids, I want ...

How do I do reflexive self-join relationships in ActiveRecord?

I'm trying to implement a social networking style friendship model and I didnt have much much luck trying to figure out the plugins available out there. I think I'll learn Rails better if I do it myself. So here's what I have : class User < ActiveRecord::Base has_many :invitee_friendships , :foreign_key => :friend_id, ...

Zend Select with self join overwriting fields

Posts and comments are stored in the same table. So to get each post and its comments we do this: $posts = $this->select()->setIntegrityCheck(false) ->from(array('post' => 'Posts'), array('*')) ->where('post.idGroup = ' . $idGroup) ->where('post.idTopic IS NULL') ->order('post.date DESC') ->limit($resultsP...

For this scenario,does a single self-join exist?

I have a table ALPHA with 2 fields GroupId,Member: GroupId | Member; A1----------A; A1----------B; A1----------C; A2----------A; A2----------B; A3----------A; A3----------D; A3----------E; Objective: Given the input of - A,B,C - I have to query the table to find if a GroupId exists for this exact set of members. So, this is what I pl...

Self-Joins, Cross-Joins and Grouping

I've got a table of temperature samples over time from several sources and I want to find the minimum, maximum, and average temperatures across all sources at set time intervals. At first glance this is easily done like so: SELECT MIN(temp), MAX(temp), AVG(temp) FROM samples GROUP BY time; However, things become much more complicated ...

Help writing a complex join query

Hello All, I am having a table orders orders ( id int unsigned not null, fcr_date TIMESTAMP, completion_date TIMESTAMP, factory_no varchar(255), vendor_no varchar(255)) Please ignore the data type typos if any. I want to write a sql query that helps me fetch the data per vendor factory. The data to fetch includes th...

MySQL join question

Hi, Can anyone tell me if it is possible to combine the following two queries into one using a self-join and, if so, how to do it? Query 1: SELECT pm.username AS user, uc.content_id AS id, value AS filename, name, moderation_status AS status, uc.parent_content_id FROM myweb.ugc...

Self Join on variable data

I have a table with the following data: Fiscal Year | Fiscal Quarter | Fiscal Week | Data 2009 | 2 | 22 | 9.5 2009 | 2 | 24 | 8.8 2009 | 2 | 26 | 8.8 2009 | 3 | 28 | 8.8 2009 | 3 | 31 | 9.1 2...

Comparing SQL Table to itself (Self-join)

I'm trying to find duplicate rows based on mixed columns. This is an example of what I have: CREATE TABLE Test ( id INT PRIMARY KEY, test1 varchar(124), test2 varchar(124) ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'A', 'B' ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' ) Now if I run this query: SELE...

Model a person-to-person relationship in Ruby-on-Rails using has_many :through

I would like to model a person's relationship to another person, where the relationship isn't necessarily hierarchical (i.e. friends & colleagues, rather than parent & children) and I am interested in capturing more detail about each relationship (e.g. notes, type of relationship, date established). Finally, I would like to use the act_a...

Update with self-join

I want to update a table to indicate that some rows are parents of others, so I added a "parentid" column to the table. The following query finds all the parents: SELECT ca1.id, ca2.id FROM contactassociations ca1 JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) where ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = '...

Can somebody explain the running total and SQL self-join in this tutorial to me?

I was reading over the tutorial here: http://www.1keydata.com/sql/sql-running-totals.html and it all made sense until it suddenly got extremely ridiculously unbelievably complicated when it got to rank, median, running totals, etc. Can somebody explain in plain English how that query results in a running total? Thanks! ...

Transitive SQL query on same table

Hey. consider d following table and data... in_timestamp | out_timestamp | name | in_id | out_id | in_server | out_server | status timestamp1 | timestamp2 | data1 |id1 | id2 | others-server1 | my-server1 | success timestamp2 | timestamp3 | data1 | id2 | id3 | my-server1 | my-server2 | success timestamp3 | time...

Scalable way of doing self join with many to many table

I have a table structure like the following: user id name profile_stat id name profile_stat_value id name user_profile user_id profile_stat_id profile_stat_value_id My question is: How do I evaluate a query where I want to find all users with profile_stat_id and profile_stat_value_id for many stats? I've tried ...

Sql query to get the GrandFather name by doing a self join

Hello all , Can any one please give me the query to get the child name and his grand fathers name For eg - if i have a table Relationships in the i have childid and fatherid columns so how will i get the grandfather, i can easily get the father name by using a join but for grandfather i need to do joins 2 times so can any one help me wi...

What are appropriate ways to represent relationships between people in a database table?

I've got a table of people - an ID primary key and a name. In my application, people can have 0 or more real-world relationships with other people, so Jack might "work for" Jane and Tom might "replace" Tony and Bob might "be an employee of" Rob and Bob might also "be married to" Mary. What's the best way to represent this in the databa...