I have a query that works in plain SQL but is not working on JPA and can't figure out why. As you can guess from the title I have a clue but I don't know how to "fix" it. Here's the actual important code: @Id @Basic(optional = false) @Column(name = "id", nullable = false) private Integer id; @Basic(optional = false) @Column(name ...
Lets say I have two separate databases, X and Y, on the same physical server. All of my queries currently run out of X. I find I have one table in Y I would like to be available to X for JOINS. So... now I keep a copy of the one table I need for both X and Y in both X and Y, but the data in Y is constantly changing, so the copy soon b...
Given the following schema: CREATE TABLE players ( id BIGINT PRIMARY KEY, name TEXT UNIQUE ); CREATE TABLE trials ( timestamp TIMESTAMP PRIMARY KEY, player BIGINT, score NUMERIC ); How would I create a SELECT that first finds the best scores from trials, then joins the name field from users? I've been able to get the score...
I'm not quite sure what this is called, but I've spent some time thinking about it and I'm not sure how to approach it. I'm sure it's really simple. I have two tables, foo and bar. They're related like this: Foo Table: | id | name -------------- | 1 | blah | 2 | blarg | 3 | blag Bar Table (the meaning of baz is irrelevant)...
I'm having a performance issue with a select statement I'm executing. Here it is: SELECT Material.* FROM Material INNER JOIN LineInfo ON Material.LineInfoCtr = LineInfo.ctr INNER JOIN Order_Header ON LineInfo.Order_HeaderCtr = Order_Header.ctr WHERE (Order_Header.jobNum = 'ttest') AND (Order_Header.revision_number = ...
the following SQL is returning every BT.Bt_Name where L.date_back is Null. I only wish to select the BT.Bt_Names where L.Bc_id is duplicated SELECT BT.Bt_Name FROM Book_Title BT INNER JOIN ( Book_Copy BC INNER JOIN Loan L ON BC.Bc_id = L.Bc_id ) ON BT.Bt_id = BC.Bt_id WHERE L.Date_back Is NULL GROUP BY BT.Bt_name HAVING COUNT(L.Bc...
What is the best way to export data from multiple tables in MySQL. I'm basically working with product details. Say a product has 150 attributes of data. How can I export that in a single row and then export it to a flat file in CSV or tabdelimited format. Getting error Too many tables; MySQL can only use 61 tables in a join /**** Get ...
I'm writing a query to get ALL of the products in the products table, and the sale price for each product IF a record exists for that item in the specials table. What I'm looking for is something like: SELECT * FROM products P IF (S.specials_date_available <= NOW() AND S.expires_date > NOW()) { // The sale has started, but has not yet ...
I am new to PHP and working on my own CMS for a project. Mostly to just give myself a project to learn the language. The CMS is working but I am just tracking down some bugs in it. So here it goes... I am trying to list all of my published articles for each section. It does this correctly but I am having an issue in the code statemen...
Hi guys I have the following structure SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE TABLE IF NOT EXISTS `sis_param_tax` ( `id` int(5) NOT NULL auto_increment, `description` varchar(50) NOT NULL, `code` varchar(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7; CREATE TABLE IF NOT EXISTS ...
I have 2 tables 1. Client 2. Operations Operations can result in: Credits or Debits ('C' or 'D' in a char field) along with date and ammount fields. I must calculate the balance for each client's account using linQ... The result should also show balance 0 for clients whom didn't make operations yet I have the following function wi...
UPDATE: Thanks to Sifu Bill's advice I have amended the SQL query. Now it returns the correct number of distinct assets (five). Is it possible to rewrite the following correlated subquery as a JOIN? SELECT TOP 100 PERCENT Asset_ID, work_order_id, status_id, downtime_hours, date_completed FROM dbo.mtvw_wo_reason1 WHERE (Asset_ID IN (SEL...
I'm trying to figure out how to generate a simple join that uses an 'or' clause contained within it, using HQL or Hibernate annotations. Here is an example of what I want the SQL to look like: select * from tableA left outer join tableB on tableA.id1 = tableB.id1 or tableA.id2 = tableB.id2 where ... I know I can do straight SQ...
I am trying to join two tables that reside in two different databases. Every time, I try to join I get the following error: An association from the table xxx refers to an unmapped class. If the tables are in the same database, then everything works fine. ...
I want to join two tables and get data against an ID from the first table and count a column record from the second table against the same ID. I want a single query which gives me that output. ...
I have two tables which I want to join together using a left outer join. However, even though my left table contains only unique values, the right table satisfies the CONDITION more than once and as such, adds extra rows to the resultset. Code to replicate problem: declare @tb1 table (c1 int) declare @tb2 table (c2 int) INSERT INTO ...
I have the following tables: Financial: PK_FinancialID FK_SchoolID School: PK_SchoolID Class: PK_ClassID FK_SchoolID ClassName Both Class and Financial have Foreign Key relationships to School. I want to make a query that would show all classes that are related to Financial rows that meet certain criteria. Initially I think...
I have a relational database with three tables. The first containts id's that relate to the second. The second contains id's that relate to the third. The third contains the results I am after. Is it possible with a single query to query an id in the first table which gives all results from the third table that relate to it? Sorry I am...
So I have built a voting system for a custom post system i wrote. I want to be able to sort by "most voted", "Most liked", etc. I have two tables. Entry: ID, Title, Post Vote: ID, EntryID, Result I want to be able to query the vote table for each entry and see how many vote's there are, and then sort the entry's by how many vote's e...
Can you please help me optimize this query. I use this query to get a list of friends along with their details and their status. It takes about 0.08 secs to process this on a Athlon X2 6000 I cant use materizlized view as well because this is frequently changing. SELECT p.userid, p.firstname, p.lastname, p.gender, p.dob, x.relationship...