join

MySql - Inserting multiple rows with a joined subquery?

This query will return a list of project IDs that represent forum threads: SELECT id FROM `proj_objects` WHERE type='fthread'; This query will subscribe a user (whose ID in the users table is '37') to the forum thread with an ID of '122': INSERT INTO `subscrips` VALUES ( 37, 122 ) ; I'd like to insert multiple rows that will subsc...

How to count similar interests in MySQL

I have 2 tables, 'interests' and 'users_interests'. 'users_interests' just has userid and interestid fields. 'interests just has an id and a name. I simply need to find userid's who have more than 3 interest ID's in common. I have been told that a Self Join is involved, but I cannot seem to get this to work. Someone said something ...

How can I combine these three queries?

I have three MySQL queries I need to combine into a single query using joins. I'm having a lot of trouble figuring out how to do it though. Here are the three queries. select sum(A.a) from A join B on A.BID = B.id where B.userID=userID select sum(C.c) from D left join (C) on (D.id=C.DID) where D.userID = userID select...

Mysql Query Incorporating Self Referential Join

Say I have four tables: ------------- features -------------- id: int name: varchar ------------------------------------- -------- feature_categories --------- feature_id: int category_id: int ------------------------------------- ----------- categories -------------- id: int name: varchar -------------------------------------...

MySQL Select all columns from one table and some from another table

How do you select all the columns from one table and just some columns from another table using JOIN? In MySQL. Thanks! ...

SQL: Expanding a row with start/end into individual rows

Hello I have a records like this: start, end , total 830 , 1300, 5 1400, 1430, 2 that I'd like to expand to: instance , total 830 , 5 831 , 5 832 , 5 ... 1299 , 5 1300 , 5 1400 , 2 1401 , 2 ... 1429 , 2 1430 , 2 How can I do this using SQL in MSSQL 2005? EDIT...

Retrieve second table as subarray in codeigniter query

I have two tables A & B, and B has a many:1 relationship with A. When querying rows from A I'd also like to have corresponding B records returned as an array and added to the result array from A, so I end up with something like this: A-ROW field field B-ITEMS item1 item2 item3 Is there a clean way to do th...

MySQL slow query with join even though EXPLAIN shows good plan

Hi, I have the following scenario: In a MySQL database, I have 2 MyISAM tables, one with 4.2 million rows, and another with 320 million rows. The following is the schema for the tables: Table1 (4.2M rows) F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY f2 varchar(40) f3 varchar(40) f4 varchar(40) f5 varchar(40) f6 smallint(6) f7 smallint(6) ...

How can I get a unique :group of a virtual attribute in rails?

I have several similar models ContactEmail, ContactLetter, etcetera. Each one belongs_to a Contact Each contact belongs_to a Company So, what I did was create a virtual attribute for ContactEmail: def company_name contact = Contact.find_by_id(self.contact_id) return contact.company_name end Question: How can I get an...

SIMPLE MySQL Join question...

Hello again! I'm a complete newbie to SQL, and I am having problems with what I assume is an easy easy SQL join statement. I have an "inventory" and an "items" table. The "inventory" table contains a user_id, an item_id, and a qty. The "items" table contains an item_id and and item_name. I want a user to get a list of all inventory ...

Query to select subcategories of categories of destinations which are active?

I have a table of destinations ( states ) which contain primary level categories and subcategories for activities. My ultimate goal is to output xml navigation links so I can link to: Each destination permalink ( ordered alphabetically by destination slug name ) Each category underneath Each subcategories underneath the category The ...

Crystal Reports SQL Server Multiple tables and Outer Join

Hi, I am trying to make a report which will show products bought by customers. I have Products, TransDetails, TransHeaders, Customers tables and i have to pick all products and for each product i have to show sales for each customer. if customer has not bought any particular product it should still be visible on report with 0 sales. user...

Mysql query staying in 'SENDING DATA' state for long time when using LEFT JOIN

I have query which is going in the SENDING DATA state for very long period of time. Can someone please help me with this : below are details Mysql Query: select a.msgId,a.senderId,a.destination,a.inTime,a.status as InStatus,b.status as SubStatus,c.deliverTime,substr(c.receipt,82,7) as DlvStatus from inserted_history a left join...

How does MySQL behave in JOIN cases where ORDER BY and LIMIT are specified and only a small number of rows need actually be JOINed?

Suppose I have the following tables: CREATE TABLE Game ( GameID INT UNSIGNED NOT NULL, GameType TINYINT UNSIGNED NOT NULL, PRIMARY KEY (GameID), INDEX Index_GameType (GameType, GameID) ) ENGINE=INNODB CREATE TABLE HighScore ( Game INT UNSIGNED NOT NULL, Score SMALLINT UNSIGNED, PRIMARY KEY (Game), INDEX ...

LINQ Join needed?

Hello all. Me again with a dumb question/scenario I need advice on. I have the following that pulls back the contents of a column: return getappropriateuserfield.tblAutoComplete .Where(p => p.MemberId == memberid && p.ACItem == acitem) .Select(p => p.ACColumn) .Distinct() ...

LINQ to Entities Join on DateTime.DayOfWeek

Imagine two tables: Shifts, RANK_S_DAY. Shifts contains a ShiftDate column which is DateTime and RANK_S_DAY has a DayOfWeek column. I need to join (int)ShiftDate.DayOfWeek equals DayOfWeek. I understand why it won't work, but I'm not quite sure how I can change it. The Exception is: The specified type member 'DayOfWeek' is not su...

Hibernate - link table with additional columns - saving in one transaction

Hi, I am learning Hibernate and just read the chapter "7.2.3 Adding columns to join tables" of the "Java Persistance with Hibernate" book. My goal is to save Item, Category and CategorizedItem in one transaction. There is a constructor there (page 305): public CategorizedItem(String username, Category category, Item item) { // Set...

Question for join

Hello Folks, I have no clue about python and started to use it on some files. I managed to find out how to do all the things that I need except for 2 things. Ipadd = string.join (line['0,1,2,4,5,6']) does not work if I make it (line[0:2]) It prints out the first two fields and thats good so far but I need field 4 and 6 also. but (line[0...

Joining very large lists

Lets put some numbers first: The largest of the list is about 100M records. (but is expected to grow upto 500). The other lists (5-6 of them) are in millions but would be less than 100M for the foreseeable future. These are always joined based on a single id. and never with any other parameters. Whats the best algorithm to join such list...

Trouble with DELETE JOIN

DELETE FROM Books INNER JOIN (Classes, Class_Books) ON (Books.ISBN = Class_Books.ISBN AND Class_Books.Class_ID = Classes.Class_ID AND Classes.Term_ID = 63) WHERE Year = '""' Gives Error: #1064 - You have an error in your SQL syntax; Replacing DELETE with SELECT it works fine though ...