Here's my SQL statement: SELECT DISTINCT a.* FROM OWU_Nomination as a, Merchants as b WHERE a.CallFlag = 'False' AND a.nominatedDate < DateAdd(hour, -24, getdate()) AND a.email != (SELECT c.Email from Members as c where c.MemberID = b.MemberID) The problem here is that the sub select after a.email != returns multi...
Hi, I have quite complicated query from which I would like to create a view. The query looks like this: select s.avg as c3, fs.bayes, fs.sure, fs.visu, fs.fstd from ( SELECT AVG(q.c3), COUNT(q.c3), q.std FROM ( SELECT std, c3, ROW_NUMBER() OVER (PARTITION BY std ORDER BY id) AS rn FROM ssims WHERE obr...
hello i'm newbie in sql (postgresql) i have 2 tables as result of 2 differen selects all calls our customer contacts number contact_id and contact_id name 3213 12 12 jonh 3213 34 16 michael 3213 43 ...
I got app that need to be recoded in CakePHP. I got following select with subselects: SELECT COUNT(*) AS item1, (SELECT COUNT(*) FROM portal_members) AS item3, (SELECT COUNT(*) FROM portal_reviews) AS item3, (SELECT COUNT(*) FROM portal_downloads) AS item4 FROM portal_articles ...
this is an attempted fix to a crystal reports use of 2 sub reports! I have a query that joins 3 tables, and I wanted to use a pair of sub selects that bring in the same new table. Here is the first of the two columns in script: SELECT ea."LOC_ID", lo."DESCR", ea."PEGSTRIP", ea."ENTITY_OWNER" , ea."PCT_OWNERSHIP", ea."BEG_BAL", ea."ADD...
I have the following query running on a MySQL, but it fails when I run it on a SQL Server database. How should it look to please SQL Server? INSERT INTO first_table (pk, data) VALUES ((SELECT value FROM second_table WHERE key = 'key'), 'other-data'); ...
Consider the following three Hibernate entities: public class Car { @OneToMany(fetch = FetchType.LAZY) @Fetch(FetchMode.SUBSELECT) private List<Wheel> wheels; } public class Wheel { @OneToOne(fetch = FetchType.LAZY) private Hubcap hubcap; } public class Hubcap { } Consider the following Criteria: Criteria criteria = ...
Consider a table like this: | txn_id | account_id I'd like to do a single query that will get me all txn_ids for all transactions where the transaction is not the most recent (highest txn_id) for the account_id . The database is MySQL 5.1, so that might imply some limitations around sub-selects. ...
Hi, I'm trying to sort a user table based on how many comments they have linked to them in a secondary comment-table, I figured a sub-select will be the best tool but I can't get the syntax correct. Users table testdata: id | user_id 1 | 1000 2 | 1001 3 | 1002 Comment table testdata id | link_id 1 | 1002 2 | 1000 3 | 1002 4 |...
I have a sql issue with column names in a subselect im guessing its because it has yet to be assigned that name but I cannot work out how to rearange it. select Distinct Captains.Name, Captains.Team, (select count(Winners.Name) from (select HomeTeamCaptain As Name from fixture where fixture.HomeTeamCaptain = Captains.Name And fixture.ma...
It's easy to find all the users ids who have trait.color = "green" but I need to find all the people who don't. The obvious way is a subselect for all the ids where not exists (select id where trait.color = "green') but I was trying to think if there's a way to do it without a subselect. Is there some trick I don't know about? sybase 12...
Hi, i'm querying a database like this: SELECT * from Log WHERE cookieId IN (select cookieId from Log WHERE someId="blafasel"); I have keys on someId and cookieId but yet the query is very slow. If I run the two queries (the outer and the inner) separated both of them or very fast. select cookieId from Log WHERE someId="blafasel" ...
I have three tables shipment (shipment_id, shipping_date) company_order (company_order_id, shipment_id, company_id) company_order_item (company_order_item_id, company_order_id, product_id) Several companies get together and aggregate orders from a single manufacturer. This aggregate order is called a "shipment". Companies order a sele...
Is is possible to accomplish the equivalent of a LEFT JOIN with subselect where multiple columns are required. Here's what I mean. SELECT m.*, (SELECT * FROM model WHERE id = m.id LIMIT 1) AS models FROM make m As it stands now doing this gives me a 'Operand should contain 1 column(s)' error. Yes I know this is possible with LEFT JOI...
Greetings stack overflow community. I've recently started building an OLAP cube in SSAS2008 and have gotten stuck. I would be grateful if someone could at least point me towards the right direction. Situation: Two fact tables, same cube. FactCalls holds information about calls made by subscribers, FactTopups holds topup data. Both ta...
I have five tables in my database. Members, items, comments, votes and countries. I want to get 10 items. I want to get the count of comments and votes for each item. I also want the member that submitted each item, and the country they are from. After posting here and elsewhere, I started using subselects to get the counts, but this q...
I've got the following SQL Statement that needs some major speed up. The problem is I need to search on two fields, where each of them is calling several sub-selects. Is there a way to join the two fields together so I call the sub-selects only once? SELECT billyr, billno, propacct, vinid, taxpaid, duedate, datepif, propdesc FROM trcd...
I've got a table that has two fields (custno and custno2) that need to be searched from a query. I didn't design this table, so don't scream at me. :-) I need to find all records where either the custno or custno2 matches the value returned from a query on the same table based on a titleno. In other words, the user types in 1234 for th...
I'm constructing a Hibernate Criterion, using a subselect as follows DetachedCriteria subselect = DetachedCriteria.forClass(NhmCode.class, "sub"); // the subselect selecting the maximum 'validFrom' subselect.add(Restrictions.le("validFrom", new Date())); // it should be in the past (null needs handling here) subselect.add(Property.f...
So I've got a very large database, and need to work on a subset ~1% of the data to dump into an excel spreadsheet to make a graph. Ideally, I could select out the subset of data and then run multiple select queries on that, which are then UNION'ed together. Is this even possible? I can't seem to find anyone else trying to do this and ...