Search Results

Search found 7706 results on 309 pages for 'inner join'.

Page 36/309 | < Previous Page | 32 33 34 35 36 37 38 39 40 41 42 43  | Next Page >

• mysql syntax how to add a third table to $query

  - by IberoMedia

  I have code: $query = "SELECT a.*, c.name as categoryname, c.id as categoryid". " FROM #__table_one as a". " LEFT JOIN #__table_two c ON c.id = a.catid"; $query .= " WHERE a.published = 1" ." AND a.access <= {$aid}" ." AND a.trash = 0" ." AND c.published = 1" ." AND c.access <= {$aid}" ." AND c.trash = 0" ; I would like to add a third table ('__some_table') for the parts of the query where a.publish, a.access and a.trash. In other words, I want these fields to be retrieved from another table, not "#__table_one", but I do not know how to incorporate the #__some_table into the current query I imagine the JOIN command can help me, but I do not know how to code mysql Thank you,

  Read the article

• mysql query to concat information from 3 tables - getting incorrect result count

  - by iPfaffy

  I have 3 tables in my database. ab_contacts id first_name last_name addressbook_id ab_addressbooks name id co_comments id link_id comment I'd like to create a query that will let me select all the contacts and comments related to them in a given addressbook. To select all the people in a given addressbook, I can use: select count(*) from ab_contacts where addressbook_id = '50'; This returns 8152 people. However, when I run my query: select ab_contacts.first_name, ab_contacts.last_name, ab_contacts.email, ab_addressbooks.name, co_comments.comments from ab_contacts JOIN ab_addressbooks ON (ab_contacts.addressbook_id = ab_addressbooks.id) JOIN co_comments ON (ab_contacts.id = co_comments.link_id) WHERE ab_contacts.addressbook_id = '50';` the format works, but I only get 1045 results. I'm sure there is something I am missing, but I cannot figure it out. Any help would be greatly appreciated.

  Read the article

• Help with MySQL Query?

  - by Devyn

  I have two tables rooms and users. I want to get only rooms.room_id, users.user_name with user_id = 1. I can get the result of all users with following sql... select rooms.room_id, rooms.user_id, users.user_name from rooms LEFT JOIN users ON rooms.user_id = users.user_id When I do like this to filter the result with user_id = 1 ... I got error. select rooms.room_id, rooms.user_id, users.user_name from rooms where rooms.user_id = 1 LEFT JOIN users ON rooms.user_id = users.user_id What should I do?

  Read the article

• Pattern for unidirectional has_many join?

  - by Kris

  It occurred to me that if I have a has_many join, where the foreign model does not have a belongs_to, and so the join is one way, then I don't actually need a foreign key. We could have a column, category_ids, which stores a marshaled Array of IDs which we can pass to find. So here is an untested example: class page < AR def categories Category.find(self.category_ids) end def categories<<(category) # get id and append to category_ids save! end def category_ids @cat_ids ||= Marshal.load(read_attribute(:category_ids)) rescue [] end def category_ids=(ids) @cat_ids = ids write_attribute(:category_ids, ids) end end page.category_ids = [1,4,12,3] page.categories = Array of Category Is there accepted pattern for this already? Is it common or just not worth the effort?

  Read the article

• MYSQL - Adding result set A to result set B? Please see my example

  - by BlackberryFan

  I have two mysql tables. They are laid out in this manner: user_info id | emailContact _______________________ 1 | [email protected] 3 | [email protected] user_biz_info id | emailContact _________________________ 8 | [email protected] 9 | [email protected] What kind of join would I use to create a result set of information like this: id | emailContact ________________________________ 1 - [email protected] 3 - [email protected] 8 - [email protected] 9 - [email protected] I have tried the following: SELECT p.id, p.emailContact, b.id, b.emailContact FROM user_info p, user_business_info b But it seems that this is an incorrect approach. Would someone be able to suggest the correct approach in this or possibly point me in the direction of some tutorials that cover this type of mysql join, as this is what I assume is needed in this case. Thanks for your time in reading through my question!!

  Read the article

• MySQL UPDATE WHERE IN for each listed value separately?

  - by Tom

  Hi, I've got the following type of SQL: UPDATE photo AS f LEFT JOIN car AS c ON f.car_id=c.car_id SET f.photo_status=1 , c.photo_count=c.photo_count+1 WHERE f.photo_id IN ($ids) Basically, two tables (car & photo) are related. The list in $ids contains unique photo ids, such as (34, 87, 98, 12). With the query, I'm setting the status of each photo in that list to "1" in the photo table and simultaneously incrementing the photo count in the car table for the car at hand. It works but there's one snag: Because the list can contain multiple photo ids that relate to the same car, the photo count only ever gets incremented once. If the list had 10 photos associated with the same car, photo_count would become 1 .... whereas I'd like to increment it to 10. Is there a way to make the incrementation occur for each photo individually through the join, as opposed to MySQL overthinking it for me? I hope the above makes sense. Thanks.

  Read the article

• How to join the results of two tables in django python

  - by user1787524

  I have two models class Weather(model.model): region = models.ForeignKey(Region) district = models.ForeignKey(District) temp_max = models.IntegerField(blank=True, null=True, verbose_name='Max temperature (C)') temp_min = models.IntegerField(blank=True, null=True, verbose_name='Min temperature (C)') and class Plan(model.model): name = tinymce_models.HTMLField(blank=True, null=True) region = models.ForeignKey(Region) district = models.ForeignKey(District) Provided for every region and district have unique row. I want to combine the result so that i can get all the columns of both tables These two Models are not related to each other. ' I need to make the join like join weather w on w.region = A.region and w.distric = A.district so that result contains all the columns in everyobject like obj.temp_max etc

  Read the article

• Can i lock a record from a join sql statment using ROWLOCK,UPDLOCK ?

  - by Andrea.Ko

  I have a store procedure to get the data i want: SELECT a.SONum, a.Seq1, a.SptNum, a.Qty1, a.SalUniPriP, a.PayNum, a.InvNum, a.BLNum, c.ETD, c.ShpNum, f.IssBan FROM OrdD a JOIN OrdH b ON a.SONum = b.SONum LEFT JOIN Invh c ON a.InvNum = c.InvNum LEFT JOIN cus d ON b.CusCod = d.CusCod LEFT JOIN BL e ON a.BLNum = e.BLNum LEFT JOIN PayMasH f ON f.PayNum = a.PayNum LEFT JOIN Shipment g ON g.ShpNum = c.ShpNum WHERE b.CusCod IN (SELECT CusCod FROM UsrInc WHERE UseID=@UserID and UseLev=@UserLvl) AND d.CusGrp = @CusGrp After i get those records into cursor, i used RowLock, UpdLock to lock all the related invoice number. SELECT InvNum FROM Invh WITH (ROWLOCK,UPDLOCK) WHERE InvNum = Can i issue locking on the table INVH at the point i select the table from a few table using join command? Any advice, please!

  Read the article

• Join Query Not Working

  - by John

  Hello, I am using three MySQl tables: comment commentid loginid submissionid comment datecommented login loginid username password email actcode disabled activated created points submission submissionid loginid title url displayurl datesubmitted In these three tables, the "loginid" correspond. I would like to pull the top 10 loginids based on the number of "submissionid"s. I would like to display them in a 3-column HTML table that shows the "username" in the first column, the number of "submissionid"s in the second column, and the number of "commentid"s in the third column. I tried using the query below but it did not work. Any idea why not? Thanks in advance, John $sqlStr = "SELECT l.username ,l.loginid ,c.commentid ,count(s.commentid) countComments ,c.comment ,c.datecommented ,s.submissionid ,count(s.submissionid) countSubmissions ,s.title ,s.url ,s.displayurl ,s.datesubmitted FROM comment AS c INNER JOIN login AS l ON c.loginid = l.loginid INNER JOIN submission AS s ON c.loginid = s.loginid GROUP BY c.loginid ORDER BY countSubmissions DESC LIMIT 10"; $result = mysql_query($sqlStr); $arr = array(); echo "<table class=\"samplesrec1\">"; while ($row = mysql_fetch_array($result)) { echo '<tr>'; echo '<td class="sitename1"><a href="http://www...com/.../members/index.php?profile='.$row["username"].'">'.stripslashes($row["username"]).'</a></td>'; echo '</tr>'; echo '<td class="sitename1">'.stripslashes($row["countSubmissions"]).'</td>'; echo '</tr>'; echo '</tr>'; echo '<td class="sitename1">'.stripslashes($row["countComments"]).'</td>'; echo '</tr>'; } echo "</table>";

  Read the article

• How grouping and totaling are done into three tables using JOIN

  - by text

  Here are my tables respondents: field sample value respondentid : 1 age : 2 gender : male survey_questions: id : 1 question : Q1 answer : sample answer answers: respondentid : 1 question : Q1 answer : 1 --id of survey question I want to display all respondents who answered the certain survey, display all answers and total all the answer and group them according to the age bracket. I tried using this query: $sql = "SELECT res.Age, res.Gender, answer.id, answer.respondentid, SUM(CASE WHEN res.Gender='Male' THEN 1 else 0 END) AS males, SUM(CASE WHEN res.Gender='Female' THEN 1 else 0 END) AS females, CASE WHEN res.Age < 1 THEN 'age1' WHEN res.Age BETWEEN 1 AND 4 THEN 'age2' WHEN res.Age BETWEEN 4 AND 9 THEN 'age3' WHEN res.Age BETWEEN 10 AND 14 THEN 'age4' WHEN res.Age BETWEEN 15 AND 19 THEN 'age5' WHEN res.Age BETWEEN 20 AND 29 THEN 'age6' WHEN res.Age BETWEEN 30 AND 39 THEN 'age7' WHEN res.Age BETWEEN 40 AND 49 THEN 'age8' ELSE 'age9' END AS ageband FROM Respondents AS res INNER JOIN Answers as answer ON answer.respondentid=res.respondentid INNER JOIN Questions as question ON answer.Answer=question.id WHERE answer.Question='Q1' GROUP BY ageband ORDER BY res.Age ASC"; I was able to get the data but the listing of all answers are not present. What's wrong with my query. I want to produce something like this: ex: # of Respondents is 3 ages: 2,3 and 6 Question: what are your favorite subjects? Ages 1-4: subject 1: 1 subject 2: 2 subject 3: 2 total respondents for ages 1-4 : 2 Ages 5-10: subject 1: 1 subject 2: 1 subject 3: 0 total respondents for ages 5-10 : 1

  Read the article

• Eclipse says I don't implement Enumeration, but I do

  - by Harm De Weirdt

  Goodmorning everybody, I'm having a little problem with a project for school. We are told to make an Iterator that implements Enumeration over a Hashmap. So i made this Enumeration: public Enumeration<Object> getEnumeration() { return new Enumeration<Object>() { private int itemsDone = 0; Collection<Long> keysCollection = getContent().keySet(); Long [] keys = keysCollection.toArray(new Long[keysCollection.size()]); @Override public boolean hasMoreElements() { if(itemsDone < getContent().size() +1 ) { return true; }else { return false; } } @Override public Object nextElement() { return getContent().get(keys[itemsDone++]); } }; } This goes in my Backpack class public class Backpack extends Item implements Carrier, Enumeration<Object>{ The hashmap is returned by getContent(). The problem now is that eclipse keeps telling me I havent implemented the methods from Enumeration. If I use the quick fix it just adds the hasMoreElements() and nextElement() dummy methods in my class. Somehow it doesn't see these methods in the inner class.. Can anyone help me please? Any help is appreciated.

  Read the article

• Efficient way to store order in mySQL for list of items

  - by ninumedia

  I want to code cleaner and more efficiently and I wanted to know any other suggestions for the following problem: I have a mySQL database that holds data about a set of photograph names. Oh, say 100 photograph names Table 1: (photos) has the following fields: photo_id, photo_name Ex data: 1 | sunshine.jpg 2 | cloudy.jpg 3 | rainy.jpg 4 | hazy.jpg ... Table 2: (categories) has the following fields: category_id, category_name, category_order Ex data: 1 | Summer Shots | 1,2,4 2 | Winter Shots | 2,3 3 | All Seasons | 1,2,3,4 ... Is it efficient to store the order of the photos in this manner per entry via comma delimited values? It's one approach I have seen used before but I wanted to know if something else is faster in run time. Using this way I don't think it is possible to do a direct INNER JOIN on the category table and photo table to get a single matched list of all the photographs per category. Ex: Summer shots - sunshine.jpg, cloudy.jpg, hazy.jpg because it was matched against 1,2,4 The iteration through all the categories and then the photos will have a O(n^2) and there has to be a better/faster way. Please educate me :)

  Read the article

• ROW_NUMBER() VS. DISTINCT

  - by ramadan2050

  Dear All, I have a problem with ROW_NUMBER() , if i used it with DISTINCT in the following Query I have 2 scenarios: 1- run this query direct : give me for example 400 record as a result 2- uncomment a line which start with [--Uncomment1--] : give me 700 record as a result it duplicated some records not all the records what I want is to solve this problem or to find any way to show a row counter beside each row, to make a [where rownumber between 1 and 30] --Uncomment2-- if I put the whole query in a table, and then filter it , it is work but it still so slow waiting for any feedback and I will appreciate that Thanks in advance SELECT * FROM (SELECT Distinct CRSTask.ID AS TaskID, CRSTask.WFLTaskID, --Uncomment1-- ROW_NUMBER() OVER (ORDER By CRSTask.CreateDate asc ) AS RowNum , CRSTask.WFLStatus AS Task_WFLStatus, CRSTask.Name AS StepName, CRSTask.ModifiedDate AS Task_ModifyDate, CRSTask.SendingDate AS Task_SendingDate, CRSTask.ReceiveDate AS Task_ReceiveDate, CRSTask.CreateDate AS Task_CreateDate, CRS_Task_Recipient_Vw.Task_CurrentSenderName, CRS_Task_Recipient_Vw.Task_SenderName, CRS_INFO.ID AS CRS_ID, CRS_INFO.ReferenceNumber, CRS_INFO.CRSBeneficiaries, CRS_INFO.BarCodeNumber, ISNULL(dbo.CRS_FNC_GetTaskReceiver(CRSTask.ID), '') + ' ' + ISNULL (CRS_Organization.ArName, '') AS OrgName, CRS_Info.IncidentID, COALESCE(CRS_Subject.ArSubject, '??? ????') AS ArSubject, COALESCE(CRS_INFO.Subject, 'Blank Subject') AS CRS_Subject, CRS_INFO.Mode, CRS_Task_Recipient_Vw.ReceiverID, CRS_Task_Recipient_Vw.ReceiverType, CRS_Task_Recipient_Vw.CC, Temp_Portal_Users_View.ID AS CRS_LockedByID, Temp_Portal_Users_View.ArabicName AS CRS_LockedByName, CRSDraft.ID AS DraftID, CRSDraft.Type AS DraftType, CASE WHEN CRS_Folder = 1 THEN Task_SenderName WHEN CRS_Folder = 2 THEN Task_SenderName WHEN CRS_Folder = 3 THEN Task_CurrentSenderName END AS SenderName, CRS_Task_Folder_Vw.CRS_Folder, CRS_INFO.Status, CRS_INFO.CRS_Type, CRS_Type.arName AS CRS_Type_Name FROM CRS_Task_Folder_Vw LEFT OUTER JOIN CRSTask ON CRSTask.ID = CRS_Task_Folder_Vw.TaskID LEFT OUTER JOIN CRS_INFO ON CRS_INFO.ID = CRSTask.CRSID LEFT OUTER JOIN CRS_Subject ON COALESCE( SUBSTRING( CRS_INFO.Subject, CHARINDEX('_', CRS_INFO.Subject) + 1, LEN(CRS_INFO.Subject) ), 'Blank Subject' ) = CRS_Subject.ID LEFT OUTER JOIN CRSInfoAttribute ON CRS_INFO.ID = CRSInfoAttribute.ID LEFT OUTER JOIN CRS_Organization ON CRS_Organization.ID = CRSInfoAttribute.SourceID LEFT OUTER JOIN CRS_Type ON CRS_INFO.CRS_Type = CRS_Type.ID LEFT OUTER JOIN CRS_Way ON CRS_INFO.CRS_Send_Way = CRS_Way.ID LEFT OUTER JOIN CRS_Priority ON CRS_INFO.CRS_Priority_ID = CRS_Priority.ID LEFT OUTER JOIN CRS_SecurityLevel ON CRS_INFO.SecurityLevelID = CRS_SecurityLevel.ID LEFT OUTER JOIN Portal_Users_View ON Portal_Users_View.ID = CRS_INFO.CRS_Initiator LEFT OUTER JOIN AD_DOC_TBL ON CRS_INFO.DocumentID = AD_DOC_TBL.ID LEFT OUTER JOIN CRSTask AS Temp_CRSTask ON CRSTask.ParentTask = Temp_CRSTask.ID LEFT OUTER JOIN Portal_Users_View AS Temp_Portal_Users_View ON Temp_Portal_Users_View.ID = AD_DOC_TBL.Lock_User_ID LEFT OUTER JOIN Portal_Users_View AS Temp1_Portal_Users_View ON Temp1_Portal_Users_View.ID = CRS_INFO.ClosedBy LEFT OUTER JOIN CRSDraft ON CRSTask.ID = CRSDraft.TaskID LEFT OUTER JOIN CRS_Task_Recipient_Vw ON CRSTask.ID = CRS_Task_Recipient_Vw.TaskID --LEFT OUTER JOIN CRSTaskReceiverUsers ON CRSTask.ID = CRSTaskReceiverUsers.CRSTaskID AND CRS_Task_Recipient_Vw.ReceiverID = CRSTaskReceiverUsers.ReceiverID LEFT OUTER JOIN CRSTaskReceiverUserProfile ON CRSTask.ID = CRSTaskReceiverUserProfile.TaskID WHERE Crs_Info.SUBJECT <> 'Blank Subject' AND (CRS_INFO.Subject NOT LIKE '%null%') AND CRS_Info.IsDeleted <> 1 /* AND CRSTask.WFLStatus <> 6 AND CRSTask.WFLStatus <> 8 */ AND ( ( CRS_Task_Recipient_Vw.ReceiverID IN (1, 29) AND CRS_Task_Recipient_Vw.ReceiverType IN (1, 3, 4) ) ) AND 1 = 1 )Codes --Uncomment2-- WHERE Codes.RowNum BETWEEN 1 AND 30 ORDER BY Codes.Task_CreateDate ASC

  Read the article

• What is the commnad line for mirc to connect and join a channel?

  - by masterkapu

  I found that I can launch irc using command line with arguments. The problem is that it won't join the specific channel. This is that lunch line: C:\Program Files\mIRC\mirc.exe /join #channel1 This command line only connects to the server, but it won't join the specific channel. How can I connect and join a channel?

  Read the article

• Querying a self referencing join with NHibernate Linq

  - by Ben

  In my application I have a Category domain object. Category has a property Parent (of type category). So in my NHibernate mapping I have: <many-to-one name="Parent" column="ParentID"/> Before I switched to NHibernate I had the ParentId property on my domain model (mapped to the corresponding database column). This made it easy to query for say all top level categories (ParentID = 0): where(c => c.ParentId == 0) However, I have since removed the ParentId property from my domain model (because of NHibernate) so I now have to do the same query (using NHibernate.Linq) like so: public IList<Category> GetCategories(int parentId) { if (parentId == 0) return _catalogRepository.Categories.Where(x => x.Parent == null).ToList(); else return _catalogRepository.Categories.Where(x => x.Parent.Id == parentId).ToList(); } The real impact that I can see, is the sql generated. Instead of a simple 'select x,y,z from categories where parentid = 0' NHibernate generates a left outer join: SELECT this_.CategoryId as CategoryId4_1_, this_.ParentID as ParentID4_1_, this_.Name as Name4_1_, this_.Slug as Slug4_1_, parent1_.CategoryId as CategoryId4_0_, parent1_.ParentID as ParentID4_0_, parent1_.Name as Name4_0_, parent1_.Slug as Slug4_0_ FROM Categories this_ left outer join Categories parent1_ on this_.ParentID = parent1_.CategoryId WHERE this_.ParentID is null Which doesn't seems much less efficient that what I had before. Is there a better way of querying these self referencing joins as it's very tempting to drop the ParentID back onto my domain model for this reason. Thanks, Ben

  Read the article

• Retrive data from two tables in asp.net mvc using ADO.Net Entity Framework

  - by user192972

  Please read my question carefully and reply me. I have two tables as table1 and table2. In table1 i have columns as AddressID(Primary Key),Address1,Address2,City In table2 i have columns as ContactID(Primary Key),AddressID(Foriegn Key),Last Name,First Name. By using join operation i can retrive data from both the tables. I created a Model in my MVC Application.I can see both the tables in enitity editor. In the ViewData folder of my solution explorer i created two class as ContactViewData.cs and SLXRepository.cs In the ContactViewData.cs i have following code public IEnumerable<CONTACT> contacts { get; set; } In the SLXRepository.cs i have following code public IEnumerable<CONTACT> GetContacts() { var contact = ( from c in context.CONTACT join a in context.ADDRESS on c.ADDRESSID equals a.ADDRESSID select new { a.ADDRESS1, a.ADDRESS2, a.CITY, c.FIRSTNAME, c.LASTNAME } ); return contact; } I am getting the error in return type Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)

  Read the article

• sqlite3 JOIN, GROUP_CONCAT using distinct with custom separator

  - by aiwilliams

  Given a table of "events" where each event may be associated with zero or more "speakers" and zero or more "terms", those records associated with the events through join tables, I need to produce a table of all events with a column in each row which represents the list of "speaker_names" and "term_names" associated with each event. However, when I run my query, I have duplication in the speaker_names and term_names values, since the join tables produce a row per association for each of the speakers and terms of the events: 1|Soccer|Bobby|Ball 2|Baseball|Bobby - Bobby - Bobby|Ball - Bat - Helmets 3|Football|Bobby - Jane - Bobby - Jane|Ball - Ball - Helmets - Helmets The group_concat aggregate function has the ability to use 'distinct', which removes the duplication, though sadly it does not support that alongside the custom separator, which I really need. I am left with these results: 1|Soccer|Bobby|Ball 2|Baseball|Bobby|Ball,Bat,Helmets 3|Football|Bobby,Jane|Ball,Helmets My question is this: Is there a way I can form the query or change the data structures in order to get my desired results? Keep in mind this is a sqlite3 query I need, and I cannot add custom C aggregate functions, as this is for an Android deployment. I have created a gist which makes it easy for you to test a possible solution: https://gist.github.com/4072840

  Read the article

• T-SQL Table Joins - Unique Situation

  - by Dimitri

  Hello Everyone. This is my first time encountering the case like this and don't quite know how to handle. Situation: I have one table tblSettingsDefinition, with fields: ID, GroupID, Name, typeID, DefaultValue. Then I have tblSettingtypes with fields TypeID, Name. And I have final table, tblUserSettings with fields SettingID, SettingDefinitionID, UserID, Value. The whole point of this is to have customizable settings. Setting can be defined for a Group or as global setting (if GroupID is NULL). It will have a default value, but if user modifies the setting, an entry is added to tblUserSettings that stores new value. I want to have a query that grabs user settings by first looking at the tblUserSettings, and if it has records for the given user, grabs them, if not retrieves default settings. But the trick is that no matter if user has settings or not, I need to have fields from other two table retrieved to know the setting's Type, Name etc... (which are stored in those other tables). I'm writing query something like this: SELECT * FROM tblSettingDefinition SD LEFT JOIN tblUserSettings US ON SD.SettingID = US.SettingDefinitionID JOIN tblSettingTypes ST ON SD.TypeID=ST.ID WHERE US.UserID=@UserID OR ((SD.GroupID IS NULL) OR (SD.GroupID=(SELECT GroupID FROM tblUser WHERE ID=@UserID))) but it retrieves settings for all users from tblUserSettings instead of just ones that match current @UserID. And if @UserID has no records in tblUserSettings, still, all user settings are retrieved instead of the defaults from tblSettingDefinition. Hope I made myself clear. Any help would be highly appreciated. Thank you.

  Read the article

• MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

  - by nibblebot

  I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.) Post 1 - Post Body blah blah - Votes: 500 Post 2 - Post Body blah blah - Votes: 400 Post 3 - Post Body blah blah - Votes: 300 Post 4 - Post Body blah blah - Votes: 200 I have 2 tables: Posts - columns - id, body, is_hidden Votes - columns - id, post_id, vote_type_id Here is the query I've tried: SELECT p.*, v.yes_count FROM posts p LEFT JOIN (SELECT post_id, vote_type_id, COUNT(1) AS yes_count FROM votes WHERE (vote_type_id = 1) GROUP BY post_id ORDER BY yes_count DESC LIMIT 0, 10) v ON v.post_id = p.id WHERE (p.is_hidden = 0) ORDER BY yes_count DESC LIMIT 0, 10 Correctness: The above query almost works. The subselect is including votes for posts that have is_hidden = 1, so when I left join it to posts, if a hidden post is in the top 10 (ranked by votes), I can end up with records with NULL on the yes_count field. Performance: I have ~50k posts and ~500k votes. On my dev machine, the above query is running in .4sec. I'd like to stay at or below this execution time. Indexes: I have an index on the Votes table that covers the fields: vote_type_id and post_id EXPLAIN id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p ALL NULL NULL NULL NULL 45985 Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 2 DERIVED votes ref VotingPost VotingPost 4 319881 Using where; Using index; Using temporary; Using filesort

  Read the article

• GET variable after doing a Join Query

  - by John

  Hello, For the code below, on the second link (http://www...com/sandbox/comments/index.php?submission='.$row["title"].'), I would like to pass $row["submissionid"], on as a GET variable. I tried this and it caused all of the code below to produce a blank result. Is there a way that I can do I want? Thanks in advance, John $sqlStr = "SELECT s.loginid ,s.title ,s.url ,s.displayurl ,l.username ,COUNT(c.commentid) countComments FROM submission s INNER JOIN login l ON s.loginid = l.loginid LEFT OUTER JOIN comment c ON s.submissionid = c.submissionid GROUP BY s.submissionid ORDER BY s.datesubmitted DESC LIMIT 10"; $result = mysql_query($sqlStr); $arr = array(); echo "<table class=\"samplesrec\">"; while ($row = mysql_fetch_array($result)) { echo '<tr>'; echo '<td class="sitename1"><a href="http://www.'.$row["url"].'">'.$row["title"].'</a></td>'; echo '</tr>'; echo '<tr>'; echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>'; echo '</tr>'; } echo "</table>";

  Read the article

• how to join tables sql server

  - by Rick

  Im having some trouble with joining two tables. This is what my two tables look like: Table 1 Customer_ID CustomerName Add. 1000 John Smith 1001 Mike Coles 1002 Sam Carter Table 2 Sensor_ID Location Temp CustIDFK 1000 NY 70 1002 NY 70 1000 ... ... 1001 1001 1002 Desired: Sensor_ID Location Temp CustIDFK 1000 NY 70 John Smith 1002 NY 70 Sam Carter 1000 ... ... John Smith 1001 Mike Coles 1001 1002 I have made Customer_ID from table 1 my primary key, created custIDFK in table 2 and set that as my foreign key. I am really new to sql server so I am still having trouble with the whole relationship piece of it. My goal is to match one customer_ID with one Sensor_ID. The problem is that the table 2 does not have "unique IDs" since they repeat so I cant set that to my foreign key. I know I will have to do either an inner join or outer join, I just dont know how to link the sensor id with customer one. I was thinking of giving my sensor_ID a unique ID but the data that is being inserted into table 2 is coming from another program. Any suggestions?

  Read the article

• Linq join two dictionaries using a common key

  - by rboarman

  Hello, I am trying to join two Dictionary collections together based on a common lookup value. var idList = new Dictionary<int, int>(); idList.Add(1, 1); idList.Add(3, 3); idList.Add(5, 5); var lookupList = new Dictionary<int, int>(); lookupList.Add(1, 1000); lookupList.Add(2, 1001); lookupList.Add(3, 1002); lookupList.Add(4, 1003); lookupList.Add(5, 1004); lookupList.Add(6, 1005); lookupList.Add(7, 1006); // Something like this: var q = from id in idList.Keys join entry in lookupList on entry.Key equals id select entry.Value; The Linq statement above is only an example and does not compile. For each entry in the idList, pull the value from the lookupList based on matching Keys. The result should be a list of Values from lookupList (1000, 1002, 1004). What’s the easiest way to do this using Linq? Thank you, Rick

  Read the article

• Please help optimizing a long running query (left outer join, with 2 subqueries)

  - by 46and2

  Hi all. The query I need help with is: SELECT d.bn, d.4700, d.4500, ... , p.`Activity Description` FROM ( SELECT temp.bn, temp.4700, temp.4500, .... FROM `tdata` temp GROUP BY temp.bn HAVING (COUNT(temp.bn) = 1) ) d LEFT OUTER JOIN ( SELECT temp2.bn, max(temp2.FPE) AS max_fpe, temp2.`Activity Description` FROM `pdata` temp2 GROUP BY temp2.bn ) p ON p.bn = d.bn; The ... represents other fields that aren't really important to solving this problem. The issue is on the the second subquery - it is not using the index I have created and I am not sure why, it seems to be because of the way TEXT fields are handled. The first subquery uses the index I have created and runs quite snappy, however an explain on the second shows a 'Using temporary; Using filesort'. Please see the indexes I have created in the below table create statements. Can anyone help me optimize this? By way of quick explanation the first subquery is meant to only select records that have unique bn's, the second, while it looks a bit wacky (with the max function there which is not being used in the result set) is making sure that only one record from the right part of the join is included in the result set. My table create statements are CREATE TABLE `tdata` ( `BN` varchar(15) DEFAULT NULL, `4000` varchar(3) DEFAULT NULL, `5800` varchar(3) DEFAULT NULL, .... KEY `BN` (`BN`), KEY `idx_t3010`(`BN`,`4700`,`4500`,`4510`,`4520`,`4530`,`4570`,`4950`,`5000`,`5010`,`5020`,`5050`,`5060`,`5070`,`5100`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `pdata` ( `BN` varchar(15) DEFAULT NULL, `FPE` datetime DEFAULT NULL, `Activity Description` text, .... KEY `BN` (`BN`), KEY `idx_programs_2009` (`BN`,`FPE`,`Activity Description`(100)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Thanks!

  Read the article

• Algorithm for finding similar users through a join table

  - by Gdeglin

  I have an application where users can select a variety of interests from around 300 possible interests. Each selected interest is stored in a join table containing the columns user_id and interest_id. Typical users select around 50 interests out of the 300. I would like to build a system where users can find the top 20 users that have the most interests in common with them. Right now I am able to accomplish this using the following query: SELECT i2.user_id, count(i2.interest_id) AS count FROM interests_users as i1, interests_users as i2 WHERE i1.interest_id = i2.interest_id AND i1.user_id = 35 GROUP BY i2.user_id ORDER BY count DESC LIMIT 20; However, this query takes approximately 500 milliseconds to execute with 10,000 users and 500,000 rows in the join table. All indexes and database configuration settings have been tuned to the best of my ability. I have also tried avoiding the use of joins altogether using the following query: select user_id,count(interest_id) count from interests_users where interest_id in (13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,508) group by user_id order by count desc limit 20; But this one is even slower (~800 milliseconds). How could I best lower the time that I can gather this kind of data to below 100 milliseconds? I have considered putting this data into a graph database like Neo4j, but I am not sure if that is the easiest solution or if it would even be faster than what I am currently doing.

  Read the article

• duplicate rows in join table with has_many => through and accepts_nested_attributes_for

  - by shalako

  An event has many artists, and an artist has many events. The join of an artist and an event is called a performance. I want to add artists to an event. This works except that I'm getting duplicate entries into my join table when creating a new event. This causes problems elsewhere. event.rb has_many :performances, :dependent => :destroy has_many :artists, :through => :performances accepts_nested_attributes_for :artists, :reject_if => proc {|a| a['name'].blank?} accepts_nested_attributes_for :performances, :reject_if => proc { |a| a['artist_id'].blank? }, :allow_destroy => true artist.rb has_many :performances, :dependent => :destroy has_many :artists, :through => :performances performance.rb belongs_to :artist belongs_to :event events_controller.rb def new @event = Event.new @event.artists.build respond_to do |format| format.html # new.html.erb format.xml { render :xml => @event } end end def create @event = Event.new(params[:event]) respond_to do |format| if @event.save flash[:notice] = 'Event was successfully created.' format.html { redirect_to(admin_events_url) } format.xml { render :xml => @event, :status => :created, :location => @event } else format.html { render :action => "new" } format.xml { render :xml => @event.errors, :status => :unprocessable_entity } end end end output Performance Create (0.2ms) INSERT INTO `performances` (`event_id`, `artist_id`) VALUES(7, 19) Performance Create (0.1ms) INSERT INTO `performances` (`event_id`, `artist_id`) VALUES(7, 19)

  Read the article

< Previous Page | 32 33 34 35 36 37 38 39 40 41 42 43  | Next Page >