Search Results

Search found 6841 results on 274 pages for 'outer join'.

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

• please help me in this query

  - by testkhan

  I have three tables (user, friends, posts) and two users (user1 and user2). When user1 adds user2 as friend then user1 can see the posts of user2 just like on Facebook. But only the posts after the date when user1 added user2 as friend. My query is like this: $sql = mysql_query("SELECT * FROM posts p JOIN friends f ON p.currentuserid = f.friendid AND p.time >= f.friend_since OR p.currentuserid='user1id' WHERE f.myid='user1id' ORDER BY p.postid DESC LIMIT 20"); it is working all the way fine but with a little problem.....!! it displays user2, user3 (all the users as friends of user1) posts for single time but shows user1 posts multiple.......i.e user2. hi user1. userssfsfsfsfsdf user1. userssfsfsfsfsdf user3. dddddddd user1. sdfsdsdfsdsfsf user1. sdfsdsdfsdsfsf but i in database it is single entry/post why it is happening........!! How can I fix it?

  Read the article

• How can I make an SQL statement that finds unassociated records?

  - by William Calleja

  I have two tables as follows: tblCountry (countryID, countryCode) tblProjectCountry(ProjectID, countryID) The tblCountry table is a list of all countries with their codes and the tblProjectCountry table associates certain countries with certain projects. I need an SQL statement that gives me a list of the countries with their country code that do NOT have an associated record in the tblProjectCountry table. so far I got to here: SELECT tblCountry.countryID, tblCountry.countryCode FROM tblProjectCountry INNER JOIN tblCountry ON tblProjectCountry.countryID = tblCountry.countryID WHERE (SELECT COUNT(ProjectID) FROM tblProjectCountry WHERE (ProjectID = 1) AND (countryID = tblCountry.countryID)) = 0 The above statement parses as correct but doesn't give the exact result I'm looking for. Can anyone help?

  Read the article

• SQL: join within same table with different 'where' clause

  - by Pmarcoen

  Ok, so the problem I'm facing is this, I have a table with 3 columns : ID, Key and Value. ID | Key | Value ================ 1 | 1 | ab 1 | 2 | cd 1 | 3 | ef 2 | 1 | gh 2 | 2 | ij 2 | 3 | kl Now I want to select the value of Keys 1 & 3 for all IDs, the return should be like this ID | 1 | 2 ================ 1 | ab | ef 2 | gh | kl So per ID 1 row containing the Values for Keys 1 & 3. I tried using 'join' but since I need to use multiple where clauses I can't figure out how to get this to work ..

  Read the article

• MySQL - getting SUM of MAX results from 2 tables

  - by SODA

  Hi, Here's my problem: I have 2 identical tables (past month data, current month data) - data_2010_03, data_2010_04: Content_type (VARCHAR), content_id (INT), month_count (INT), pubDate (DATETIME) Data in month_count is updated hourly, so for each combination of content_type and content_id we insert new row, where value of month_count is incrementally updated. Now I try something like this: SELECT MAX(t1.month_count) AS max_1, MAX(t2.month_count) AS max_2, SUM(max_1 + max_2) AS result, t1.content_type, t1.content_id FROM data_2010_03 AS t1 JOIN data_2010_04 AS t2 ON t1.content_type = t2.content_type AND t1.content_id = t2.content_id WHERE t2.pubDate < '2010-04-08' AND t1.content_type = 'video' GROUP BY t1.content_id ORDER BY result desc, max_1 desc, max_2 desc LIMIT 0,10 I get an error "Unknown column 'max_1' in 'field list'. Please help.

  Read the article

• Can't enumerate LinQ results with left join

  - by nvtthang

  var itemSet = from item in da.GetList<Models.account>() join file in objFileStorageList on item.account_id equals file.parent_id into objFile from fileItem in objFile.DefaultIfEmpty() where item.company != null && item.company.company_id == 123 orderby item.updatedDate descending select new { Id = item.account_id, RefNo = item.refNo, StartDate = item.StartDate , EndDate = item.EndDate , Comment = item.comment, FileStorageID = fileItem != null ? fileItem.fileStorage_id : -1, Identification = fileItem != null ? fileItem.identifier : null, fileName = fileItem != null ? fileItem.file_nm : null }; It raises error message when I try to enumerate through collection result from Linq query above. LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable1[SCEFramework.Models.fileStorage] DefaultIfEmpty[fileStorage](System.Collections.Generic.IEnumerable1[SCEFramework.Models.fileStorage])' method, and this method cannot be translated into a store expression foreach (var item in itemSet) { string itemRef= item.RefNo; } Please suggest me any solutions. Thanks in advance.

  Read the article

• Select the first row in a join of two tables in one statement

  - by Oscar Cabrero

  hi i need to select only the first row from a query that joins tables A and B, on table B exist multiple records with same name. there are not identifiers in any of the two tables. i cannt change the scheme either because i do not own the DB TABLE A NAME TABLE B NAME DATA1 DATA2 Select Distinct A.NAME,B.DATA1,B.DATA2 From A Inner Join B on A.NAME = B.NAME this gives me NAME DATA1 DATA2 sameName 1 2 sameName 1 3 otherName 5 7 otherName 8 9 but i need to retrieve only one row per name NAME DATA1 DATA2 sameName 1 2 otherName 5 7 i was able to do this by adding the result into a temp table with a identity column and the select the Min Id per name. the problem here is that i require to do this in one single statement. this is a DB2 database thanks

  Read the article

• An analog of String.Join(string, string[]) for IEnumerable<T>

  - by abatishchev

  class String contains very useful method - String.Join(string, string[]). It creates a string from an array, separating each element of array with a symbol given. But general - it doesn't add a separator after the last element! I uses it for ASP.NET coding for separating with "<br />" or Environment.NewLine. So I want to add an empty row after each row in asp:Table. What method of IEnumerable<TableRow> can I use for the same functionality?

  Read the article

• MySQL cross table regular expression match

  - by Josef Sábl

  I have a web application and I am working on engine that analyzes referals. Now I have table with pageviews along with referes that looks something like this: pv_id referer ------------------------------------------------------------ 5531854534 http://www.google.com/search?ie=UTF-8... 8161876343 http://google.cn/search?search=human+rights 8468434831 http://search.yahoo.com/search;_... The second table contains sources definitions like: source regex ------------------------------------------------------------ Google ^https?:\/\/[^\/]*google\.([a-z]{2,4})(\/.*|)$ Yahoo ^https?:\/\/[^\/]*yahoo\.com(\/.*|)$ What I want is third table created by joinin these two: pv_id source ------------------------------------------------------------ 5531854534 Google 8161876343 Google 8468434831 Yahoo How to join these tables with regular expression?

  Read the article

• Rails way for querying join table in has_and_belongs_to_many

  - by Michelle

  I have a user model and a role model with a has_and_belongs_to_many reliationship. The join table is roles_users (two columns - the PK of the user and the role) and has no corresponding model. I want to have a method that returns all users with a given role. In SQL that'd be something like SELECT u.id FROM role.r, roles_users ru WHERE r.role_id = #{role.id} AND r.role_id = ru.role_id I see that Rails' activerecord has a find_by_sql method, but it's only expecting one results to be returned. What is the "Rails Way" to give me a list of users with a given role e.g. def self.find_users_with_role(role) users = [] users << # Some ActiveRecord magic or custom code here..? end

  Read the article

• Improving performance in this query

  - by Luiz Gustavo F. Gama

  I have 3 tables with user logins: sis_login = administrators tb_rb_estrutura = coordinators tb_usuario = clients I created a VIEW to unite all these users by separating them by levels, as follows: create view `login_names` as select `n1`.`cod_login` as `id`, '1' as `level`, `n1`.`nom_user` as `name` from `dados`.`sis_login` `n1` union all select `n2`.`id` as `id`, '2' as `level`, `n2`.`nom_funcionario` as `name` from `tb_rb_estrutura` `n2` union all select `n3`.`cod_usuario` as `id`, '3' as `level`, `n3`.`dsc_nome` as `name` from `tb_usuario` `n3`; So, can occur up to three ids repeated for different users, which is why I separated by levels. This VIEW is just to return me user name, according to his id and level. considering it has about 500,000 registered users, this view takes about 1 second to load. too much time, but is becomes very small when I need to return the latest posts on the forum of my website. The tables of the forums return the user id and level, then look for a name in this VIEW. I have registered 18 forums. When I run the query, it takes one second for each forum = 18 seconds. OMG. This page loads every time somebody enter my website. This is my query: select `x`.`forum_id`, `x`.`topic_id`, `l`.`nome` from ( select `t`.`forum_id`, `t`.`topic_id`, `t`.`data`, `t`.`user_id`, `t`.`user_level` from `tb_forum_topics` `t` union all select `a`.`forum_id`, `a`.`topic_id`, `a`.`data`, `a`.`user_id`, `a`.`user_level` from `tb_forum_answers` `a` ) `x` left outer join `login_names` `l` on `l`.`id` = `x`.`user_id` and `l`.`level` = `x`.`user_level` group by `x`.`forum_id` asc USING EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 530415 4 DERIVED n1 ALL NULL NULL NULL NULL 114 5 UNION n2 ALL NULL NULL NULL NULL 2 6 UNION n3 ALL NULL NULL NULL NULL 530299 NULL UNION RESULT ALL NULL NULL NULL NULL NULL 2 DERIVED t ALL NULL NULL NULL NULL 3 3 UNION r ALL NULL NULL NULL NULL 3 NULL UNION RESULT ALL NULL NULL NULL NULL NULL Somebody can help me or give a suggestion?

  Read the article

• querying databases on same server with linq

  - by geoff swartz

  In normal sql I could do joins on tables in different databases as long as they were on the same server (or linked servers). In linq I can't figure out how to do that. Is this possible? For example, if I have a database called db1 and another called db2. db1 has a table called people and db2 has a table called address I could do something like... select a.addressline1, p.firstname from db1.dbo.people p inner join db2.dbo.address a on p.peopleid = a.peopleid Is this possible with linq? Thanks.

  Read the article

• Issue with Java join() method.

  - by gmunk

  First of all here are some code snippets: http://pastebin.com/9ZCwekXs http://pastebin.com/TtLLXPYP I'm trying to animate some images. The thing is that I want the main thread to wait for the animation thread to finish and then to continue. I searched around, read a little bit and decided to use the join() method. It perfectly waits for the thread to finish but I doesn't animate correctly. The repaint() method gets called 2 times instead of nine. I think maybe the problem is because I used singletons. Here is the singleton implementation. http://pastebin.com/bA3qLZJE

  Read the article

• An analog of String.Join(string, string[]) for List<T> or other generic enumerable

  - by abatishchev

  class String contains very useful method - String.Join(string, string[]). It creates a string from an array, separating each element of array with a symbol given. But general - it doesn't add a separator after the last element! I uses it for ASP.NET coding for separating with "<br />" or Environment.NewLine. So I want to add an empty row after each row in asp:Table. What method of IEnumerable<TableRow> can I use for the same functionality?

  Read the article

• Help with SQL Server query

  - by Travis

  Sorry* this is what I should have put My query is creating duplicate entries for any record that has more than 1 instance (regardless of date) <asp:SqlDataSource ID="EastMonthlyHealthDS" runat="server" ConnectionString="<%$ ConnectionStrings:SNA_TRTTestConnectionString %>" SelectCommand="SELECT [SNA_Parent_Accounts].[Company], (SELECT [Monthly_HIP_Reports].[AccountHealth] from [Monthly_HIP_Reports] where ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC) AND ([SNA_Parent_Accounts].[CompID] = [Monthly_HIP_Reports].[CompID])) as [AccountHealth], [SNA_Parent_Accounts].[CompID] FROM [SNA_Parent_Accounts] LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID] WHERE (([SNA_Parent_Accounts].[Classification] = 'Business') OR ([SNA_Parent_Accounts].[Classification] = 'Business Ihn')) AND ([SNA_Parent_Accounts].[Status] = 'active') AND ([SNA_Parent_Accounts].[Region] = 'east') ORDER BY [SNA_Parent_Accounts].[Company]"> <SelectParameters> <asp:ControlParameter ControlID="ddMonths" Name="ToDtRFC" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> Using SELECT DISTINCT appears to correct the problem, but I don't consider that a solution. There are no duplicate entries in the database. So it appears my query is superfically creating duplicates. The query should grab a list of companies that meet the criteria in the where clause, but also grab the Health status for each company in that particular [YearMonth] if present which is what the subquery is for. If an entry for that YearMonth is not present, then leave the Health status blank. but as stated earlier.. if you have an entry say for 2009-03 for CompID 2 and an entry for 2009-04 for CompID 2.. Doesn't matter what month you select it will list that company 2-3 times.

  Read the article

• displaying values from join query array

  - by THOmas

  I have used a join query for retrieving value from two tables one is blogquestion and userdetails I wrote this query $this->questions = Doctrine_Query::create() ->select('b.question_id,b.user_id,b.question_title,b.question_tags,b.created_at,u.id,u.name') ->from('BlogQuestion b') ->leftJoin('b.UserDetails u') ->execute(); In the template iam displaying the result using a foreach <?php foreach($questions as $quest):?> echo $quest->getQuestionTitle() echo $quest->getQuestionTags() echo $quest->getName() <?php endforeach?> title is getting from the blogquestion table and name is in usredetails table iam getting the eerror Unknown record property / related component "name" on "BlogQuestion"

  Read the article

• Mysql - multiple values and between

  - by realshadow

  Hey, I need to select 10 products and display them. Each product has 3 different prices. The select to get the prices looks like this: SELECT * FROM products_loans WHERE CODE IN('10X15/12', '10X15/Q10-10', '10X15/Q20-10') AND 550 BETWEEN PRICE_FROM AND PRICE_TO; Where 550 is the base price. Now this select returns 3 rows, but I want to modify it so it will return 30 results. I dont like the idea to execute 10 queries at once. I know I can easily achieve that with "OR", but I would like to ask if there is some other more elegant way to this. The select "should" look like this: SELECT * FROM products_loans WHERE KOD IN('10X15/12', '10X15/Q10-10', '10X15/Q20-10') AND (550, 325, 780) BETWEEN CENA_OD AND CENA_DO; Note that there is no "price" column or anything in the table which I could use to do a JOIN and I cant modify the table.

  Read the article

• MySQL Query, limit output display according/only to associated ID!

  - by Jess

  So here's my situation. I have a books table and authors table. An author can have many books... In my authors page view, the user (logged in) can click an author in a tabled row and be directed to a page displaying the author's books (collected like this URI format: viewauthorbooks.php?author_id=23), very straight forward... However, in my query, I need to display the books for the author only, and not all books stored in the books table (as i currently have!) As I am a complete novice, I used the most simple query of: SELECT * FROM tasks_tb :)....this returns the books for me, but returns every single value (book) in the database, and not ones associated with the selected author. And when I click a different author the same books are displayed for them...I think everyone gets what I'm trying to achieve, I just don't know how to perform the query. I'm guessing that I need to start using more advanced query clauses like INNER JOIN etc. Anyone care to help me out :)

  Read the article

• mysql eliminate responses under certain condition with join

  - by Dustin

  Forgive me if this is an easy question. I teach classes and want to be able to select those students who have taken one class, but not another class. I have two tables: lessons_slots which is the table for every class such as: -------------------- -ID name slots- -1 basics 10 - -2 advanced 10 - -3 basics 10 - --------------------- The other table is class_roll, which holds enrollment info, such as: -------------------- -sID classid firstname lastname- -1 1 Jo Schmo -2 1 Person Two ... -13 2 Jo Schmo --------------------- What I want to do, I select everyone who has not had the advanced class (for example). I've tried doing SELECT * FROM lessons_slots LEFT JOIN class_roll ON lessons_slots.ID = class_roll.classid WHERE lessons_slots.name != 'advanced' But that doesn't work. Any ideas?

  Read the article

• Fill data gaps - UNION, PARTITION BY, or JOIN?

  - by Dave Jarvis

  Problem There are data gaps that need to be filled. Would like to avoid UNION or PARTITION BY if possible. Query Statement The select statement reads as follows: SELECT count( r.incident_id ) AS incident_tally, r.severity_cd, r.incident_typ_cd FROM report_vw r GROUP BY r.severity_cd, r.incident_typ_cd ORDER BY r.severity_cd, r.incident_typ_cd Data Sources The severity codes and incident type codes are from: severity_vw incident_type_vw The columns are: incident_tally severity_cd incident_typ_cd Actual Result Data 36 0 ENVIRONMENT 1 1 DISASTER 27 1 ENVIRONMENT 4 2 SAFETY 1 3 SAFETY Required Result Data 36 0 ENVIRONMENT 0 0 DISASTER 0 0 SAFETY 27 1 ENVIRONMENT 0 1 DISASTER 0 1 SAFETY 0 2 ENVIRONMENT 0 2 DISASTER 4 2 SAFETY 0 3 ENVIRONMENT 0 3 DISASTER 1 3 SAFETY Question How would you use UNION, PARTITION BY, or LEFT JOIN to fill in the zero counts?

  Read the article

• A linq join combined with a regex

  - by Geert Beckx

  Is it possible to combine these 2 queries or would this make my code too complex? Also I think there should be a performance gain by combining these queries since I think in the near future my source table could be over 11000 records. This is what i came up with so far : Dim lit As LiteralControl ' check characters not in alphabet Dim r As New Regex("^[^a-zA-Z]+") Dim query = From o In source.ToTable _ Where r.IsMatch(o.Field(Of String)("nam")) lit = New LiteralControl(String.Format("letter: {0}, count: {1}<br />", "0-9", query.Count)) plhAlpabetLinks.Controls.Add(lit) Dim q = From l In "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToLower.ToCharArray _ Group Join o In source.ToTable _ On l Equals o.Field(Of String)("nam").ToLowerInvariant(0) Into g = Group _ Select l, g.Count ' iterate the alphabet to generate all the links. For Each letter In q.AsEnumerable lit = New LiteralControl(String.Format("letter: {0}, count: {1}<br />", letter.l, letter.Count)) plhAlpabetLinks.Controls.Add(lit) Next Kind regards, G.

  Read the article

• Why is this MySQL Join Statement not Working?

  - by Timmy

  Here is my code: $query2 = mysql_query("SELECT * FROM categories WHERE parent = $id JOIN SELECT * FROM posts WHERE main_nav_page = '$idTwo'"); while ($row2 = mysql_fetch_assoc($query2)) { $id = $row2['id']; $name = $row2['name']; $slug = $row2['slug']; $subMenuOrder = $row2['sub_menu_order']; echo "<tr>\n"; echo "<td>&nbsp; -- $name</td>\n"; echo "</tr>\n"; } Is my syntax wrong? EDIT: the error message is: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/studentw/public_html/new_nav.php on line 30

  Read the article

• Adding a Third Table to a Two-Table Join Query

  - by John

  Hello, The query below works just fine. It pulls fields from two MySQL tables, "comment" and "login". It does this for rows where "username" in the table "login" equals the variable "$profile." It also pulls fields for rows where "loginid" in the table "comment" equals the "loginid" that is also being pulled from "login." I would like to pull data from a third table called "submission," which has the following fields: submissionid loginid title url displayurl datesubmitted I would like to pull fields from rows in "submission" where "loginid" equals the "loginid" that is already being pulled from the other two tables, "login" and "comment." How can I do this? Thanks in advance, John Query: $sqlStrc = "SELECT l.username, l.loginid, c.loginid, c.commentid, c.submissionid, c.comment, c.datecommented FROM comment AS c INNER JOIN login AS l ON c.loginid = l.loginid WHERE l.username = '$profile' ORDER BY c.datecommented DESC LIMIT 10";

  Read the article

• How Can I Join Two DB Tables and Return Lowest Price From Joined Table

  - by Jason

  I have two tables, the first table has the product and the second table the prices. The price table could have more than one price per product but I only want to display the lowest. But I keep getting all the prices returned and I'm having trouble figuring out how to do it. this is what I get returned in my query: SELECT * FROM products AS pr JOIN prices AS p ON pr.id = p.product_id WHERE pr.live = 1 AND p.live = 1 id product1 name description £100 id product1 name description £300 id product1 name description £200 id product2 name description £50 id product2 name description £80 id product2 name description £60 id product3 name description £222 id product3 name description £234 id product3 name description £235 but I'm after: id product1 name description £100 id product2 name description £50 id product3 name description £222 Any help would be appreciated

  Read the article

• Select 2 Rows from Table when COUNT of another table

  - by Marcus

  Here is the code that I currently have: SELECT `A`.* FROM `A` LEFT JOIN `B` ON `A`.`A_id` = `B`.`value_1` WHERE `B`.`value_2` IS NULL AND `B`.`userid` IS NULL ORDER BY RAND() LIMIT 2 What it currently is supposed to do is select 2 rows from A when the 2 rows A_id being selected are not in value_1 or value_2 in B. And the rows in B are specific to individual users with userid. What I need to do is make it also so that also checks if there are already N rows in B matching a A_id (either in value_1, or value_2) and userid, and if there are more than N rows, it doesn't select the A row.

  Read the article

• COUNT issue across multiple tables

  - by Kim

  I am trying to count across 2 tables and I dont see whats wrong with my query yet I get a wrong result. User 2 does not exist in table_two, so the zero is correct. SELECT t1.creator_user_id, COUNT(t1.creator_user_id), COUNT(t2.user_id) FROM table_one AS t1 LEFT JOIN table_two AS t2 ON t2.user_id = t1.creator_user_id GROUP BY t1.creator_user_id, t2.user_id Actual result 1 192 192 2 9 0 Expected result 1 16 12 2 9 0 The result indicate a missing group by condition, but I already got both fields used. Where am I wrong ? Also, can I sum up all users that doesnt exist in table_two for t1 ? Like user 3 exists 21 times in t1, then the results would be: 1 16 12 (users with > 0 in t2 will need their own row) 2 30 0 (user 2=9 + user 3=21 => 30) Its okay for the user Id to be wrong for sum of t1 for all users with 0 in t2. If not possible, then I'll just do two queries.

  Read the article

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