I have Table1 and Table2 in the form of IEnumerable. Both the tables have columns Column1 and Column2 I would like to do a left outer join on Column1 and would like to get a count of the rows present in Table2 and load the records into a DataTable. I tried the following query var query = from p in Table1 join q in...
Is it possible to do a CROSS JOIN between 2 tables, followed by a LEFT JOIN on to a 3rd table, followed by possibly more left joins? I am using SQL Server 2000/2005. I am running the following query, which is pretty straightForward IMO, but I am getting an error. select P.PeriodID, P.PeriodQuarter, P.PeriodYear, ...
Oi Right to the problem. SELECT *,t.id AS threadid FROM threads t LEFT JOIN players p on p.id = t.last_poster WHERE t.boardid = $boardid I have two fields in threads called posterid and lastposterid. Which are the IDs of the thread starter / last poster. What I want to do is to get their names from players table. But how? ...
Hello, I have wasted my whole afternoon on this so any help is greatly appreciated. Table Schema Table Name: file_manager_folder Rows: id , parentId, name My query simulates moving a folder into another folder and accepts an array using IN(?). I want my update to only 'move' a folder if there is not already a folder with the same...
I have a website where visitors can leave comments. I want to add the ability to answer comments (i.e. nested comments). At first this query was fast but after I populated the table with the existing comments (about 30000) a simple query like: SELECT c.id, c2.id FROM (SELECT id FROM swb_comments WHERE pageId = 1411 ...
I'm sure this is something really simple that I'm overlooking, but MS SQL is new to me -- I am (or at least thought I was) fairly comfortable with basic MySql though. SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17, dl.day_19 FROM lnktrk_links AS l, lnktrk_hourly AS h, lnktrk_daily AS dl LEFT JOIN ...
I have a Posts table and PostComments table of a blog system. I want to count and sort the posts by comment count but my query won't work.: SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description, Posts.Hits, (SELECT Count(CommentID) FROM PostComments WHERE PostComments.PostID=Posts.PostID AND PostComments.IsApproved=Tru...
I have a table Books, where I store Book data (ISBN's, Titles, Authors, etc.). To tell which books are editions of each other I have a field Edition_Group_ISBN, which is an arbitrary ISBN from the group. I'm having trouble getting this query, which is supposed to give the Book data and the number of other Editions based on the ISBN, to...
I have a query. DELETE FROM A WHERE i NOT IN ( SELECT i FROM B WHERE j = 1 UNION select i from C UNION select i from D ); basically delete all rows in A where field i does not occur in tables B, C or D. If it was just DELETE FROM A WHERE i NOT IN ( SELECT i FROM B ); then that could be done easility with a left join ...
Take the case of two tables: tbl_product and tbl_transaction. tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc. I need to display a web-page showing 10 products and for each product, the last 5 transactions. So far, n...
Sorry, I am pretty much an SQL noob. This has to work in MSFT SQL, Oracle as well as Sybase. In the following snippet I need to change an inner join between IJ and KL on IJ.PO_id = KL.PO_id into a left join also on IJ.PO_id = KL.PO_id. So, I believe I have to re-factor this. Well, implicit joins are not the most readable, at least in my ...
Hello can anybody see why this query fails? SELECT A.idAd, A.ads_in_Cat, A.title, A.currency, A.price, A.in_dpt, A.description, A.featured FROM ads A LEFT JOIN featured F ON F.ad = A.idAd INNER JOIN dept D ON D.id_dept = A.in_dpt INNER JOIN sub_cat_ad S ON S.id_sub_cat = A.ads_in_Cat INNER JOIN cat_ad C ON C.idCat_ad = S.from_cat_ad ...
I write a statistical application: I have a dataset with many tables; all of them have one key field (int) – the first field, and an unknown number of other fields (double). I already built an output table, with the same structure. I plan to join the tables that the user selects, iteratively, to the output table, one at a time. It's a l...
Table 1 ID 1 2 3 Table 2 ID date opt 1 1/1/10 1 1 1/2/10 0 2 1/1/10 1 I Want ID date opt 1 1/2/10 0 2 1/1/10 1 How do I join these 2 tables? Just match all the ID's in table 1 with their most recent opt in table 2. Without partitions, please. I'm in sql 2005. Thanks. ...
I'm sure I'm being stupid but I can't seem to figure this one out... I have two tables: department( did, name ) employee( eid, first, last, did ) they have corresponding entities JPA managed entites Department and Employee. Employee has a Deparment field, Department doesn't maintain an Employee list. What I want to do though is find a...
On part of my site, users enter multiple search terms. Each of these search terms may return 0 rows, or return several. I perform a UNION on all the searches and get the rows. Thing are much easier for me if I can get all the search terms in the response, regardless of whether they return any rows. Is there a way to essentially LEFT ...
I'm having difficulty with a query which displays records according to their fill rate. For instance, a vacancy can have no bookings or some bookings. If a vacancy has bookings, they can be in the form of 'active [1]', 'pending [0]'. The query I have written so far works if the vacancy has booking records but I can't get it to work if ...
I have 3 tables A, B and C I am doing this :- /* There is no relation between Table A and Table B . Table A is just used to provide values to C.Profile */ 1st step ) D <---- Select * from C where Profile=1 // want to give a specific ProID(I have successfully retrieved it from A table) 2nd Step ) Output <--- Select B.sname,D.Status fr...
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...
I have the following query: SELECT products_categories.categoryID, name, COUNT(*) AS itemCount FROM products_categories LEFT JOIN products_to_categories ON products_to_categories.categoryID = products_categories.categoryID GROUP BY products_categories.categoryID But still there's a problem: categories with no products in them return i...