FAQ Database Discussion Community


Nested Join in Subquery and failing correlation

mysql,sql,join,subquery
I have 3 tables sc_user, sc_cube, sc_cube_sent I wand to join to a user query ( sc_user) one distinct random message/cube ( from sc_cube ), that has not been sent to that user before ( sc_cube_sent), so each row in the result set has a disctinct user id and a...

SQL Reference Large Equation in Select Statement

mysql,sql,sql-server,performance,subquery
I have a large equation in a select statement that I need to reference in a where statement I think it is eating up my efficiency, I do not want to reference it more than I need to. SELECT 'HeaderDetail' AS rowType, ... , [large CASE/WHEN statement] AS Tracking ......

Combining SQL grouped and ungrouped results with a cross join?

sql,subquery,filemaker
I have inherited two tables, where the data for one is in hours, and the data for the other is in days. One table has planned resource use, the other holds actual hours spent Internal_Resources | PeopleName | NoOfDays | TaskNo | |------------|----------|--------| | Fred | 1 | 100 |...

How to update multiple columns in the same table with the same sub-query in Oracle SQL

oracle,sql-update,subquery
Is there a better way of writing this general type update in Oracle: UPDATE table1 SET c1 = ( SELECT d1 FROM table2 WHERE table1.id = table2.id ), c2 = ( SELECT d2 FROM table2 WHERE table1.id = table2.id ) The update above is only an example. The sub-select could...

Rewriting a slow SQL (sub) query in JOIN

mysql,sql,join,subquery
So I've got massive slow SQL query and I've narrowed it down to a slow sub-query, so I want to rewrite it to a JOIN. But I'm stuck... (due to the MAX and GROUP BY) SELECT * FROM local.advice AS aa LEFT JOIN webdb.account AS oa ON oa.shortname = aa.shortname...

MySQL subquery or join

mysql,sql,join,subquery
I have to migrate Date from one table to another. This is the source Table. For every testid there are 2 channels with 3 rows per channel. id value1 value2 testid channel 1 123 567 789 asd 2 125 587 789 asd 3 126 596 789 asd 4 569 12...

In MySQL, get number of pregnant women who received 3 ANC checkups

mysql,stored-procedures,subquery
I am working on a project where it is required to find out the total number of pregnant women who have received 3 ANC(Ante-Natal Care) checkups done. A pregnant women is said to receive 3 ANC checkups if any 3 of the 4 columns(visit1_date,visit2_date,visit3_date and visit4_date) have some value. For...

NESTED SELECT QUERY WITH CASE

sql,oracle,select,subquery,case
Please have a look at the query. The issue I am facing is this: The FROM_LOCATION,TO_LOCATION in THEN clause needs to be the value from B_TARIFFS, However when I prefix it with B_TARIFFS.FROM_LOCATION and B_TARIFFS.TO_LOCATION , I get an error that ORA-00904: Invalid identifier.. If it is available in ELSE...

Subquery aggregate function with SUM(CASE SUBQUERY)

sql,sql-server,sum,subquery,aggregate-functions
I'm getting an error while executing my query Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Code: SELECT S.id, SUM(CASE WHEN sc.coverage IN (SELECT number FROM ArrayOfIntegersFromString(@dynamicData)) THEN 1 ELSE 0 END) as sm FROM Storefronts s LEFT JOIN StorefrontCoverages sc ON s.id =...

Using a subquery in SELECT clause with a join of a table to itself

oracle,join,subquery
Trying to create a subquery in the SELECT clause (using Oracle 11g). The subquery includes a join of a table to itself. I'm getting an "ORA-01427: single-row subquery returns more than one row" error message (my worst enemy...). Here's my code: SELECT e.ISBN, (SELECT p2.PAGE_NUMBERS FROM PAGE_NUMBERS_TABLE p1 INNER JOIN...

Oracle sql search rows containing one or more keyword and sum by its keyword

oracle,subquery
I want to calculate sum by a different text, but the subquery keeps returning multiple rows, instead of a single row. I was doing like this: select sub.aid, sum (case when sub.text like '%abc' then sub.value end), sum (case when sub.text like '%def' then sub.value end), ... from ( select...

How to use subquery table alias in WHERE clause in MySQL

mysql,subquery
SELECT invoice_id, sum_amount FROM (SELECT invoice_id, SUM(amount) AS sum_amount FROM invoice GROUP BY invoice_id) AS TEMP_TABLE WHERE sum_amount in (SELECT MAX(sum_amount) FROM TEMP_TABLE); When I tried to use the TEMP_TABLE, an error occurred and said TEMP_TABLE doesn't exist. Why doesn't it work? I think the execution order is "FROM" then...

finding length of longest coulmn in a database - SELECT subquery

select,subquery,sqlbase
My skills in SQL are limited: I have a database (SQLBase in this case) that has a couple of LONGVARs in columns. I'm searching for the actual length of all COLUMNS that have a particular type. SELECT tbname,name FROM sysadm.syscolumns where coltype='LONGVAR'; The above statement works. It gives me all...

Mysql query using subselects needs to use joins or exists

mysql,sql,performance,join,subquery
NOTE If your going to downvote my question that I clearly put a lot of time into, can you please at least tell me why so I can avoid any mistakes in the future? Thank you. Update At the suggestion of @DRapp I am adding an explanation as to what...

SubQuery execution time : localhost = 2 seconds / server = 98+ seconds

mysql,subquery
May I ask for some help with a subQuery, please? When I run the query on my local XAMP mySQL db, the query takes 2 seconds to complete. Yet with an identical db on my webserver the same query takes 98 seconds plus to return the same results. When I...

SQL Select within Sub Query

sql-server,select,subquery,select-case
Hi My query is as follows, and contains a subquery within it: ALTER PROCEDURE [dbo].[POBalance] @PONumber NVARCHAR(50) AS BEGIN SELECT CASE WHEN X.STATUS = 'False' THEN ( SELECT A.Description ,C.qty AS POqty ,B.Qty AS PDQty ,CASE WHEN A.partialflag = 'false' THEN '0' ELSE A.qty END AS Balance ,A.Unit ,A.Unitprice ,A.Partialflag...

SQL: Get the value of friend column based on current user

mysql,sql,subquery
I have a table: | friendID | friendA | friendB | relation | ------------------------------------------- 1 2 4 1 2 3 4 1 3 4 8 2 4 4 9 1 I want to run a query in that I can retrieve a list of friends who are not the current...

Problems With FOR XML AUTO

sql,asp.net,sql-server,subquery,sqlxml
i have the following Query: SELECT QuestionID_PK , QuestionTitle , ( SELECT dbo.Tags.TagID_PK , dbo.Tags.TagTitle , dbo.Tags.TagTitle_EN FROM dbo.Question_Tag_Rel INNER JOIN dbo.Questions ON dbo.Question_Tag_Rel.QuestionID_FK = dbo.Questions.QuestionID_PK INNER JOIN dbo.Tags ON dbo.Question_Tag_Rel.TagID_FK = dbo.Tags.TagID_PK AND dbo.Questions.QuestionID_PK = '2116' FOR XML PATH('') , TYPE , ELEMENTS ) AS Tags FROM Questions WHERE...

SQL WHERE Subquery in Field List

sql,subquery,fieldlist
I have query like: SELECT field FROM table WHERE ( SELECT COUNT(*) FROM table2 WHERE table2.field = table.field ) != ( SELECT COUNT(*) FROM table3 WHERE table3.field = table.field ) Now I want to have those WHERE subqueries in my field list like: SELECT field, count1, count2 FROM table WHERE...

MYSQL LIMIT on COUNT of DISTINCT row

mysql,subquery,limit
Is there a way to set a LIMIT equal to the COUNT of DISTINCT entries in a row? I have below query and it all work but the LIMIT. My table will have a varying ammount of DISTINCT seriesID and I want the last Episode of the Last Season in...

Erroneous query works when used as subquery - how? Why?

sql,sql-server,select,subquery,sql-server-2000
Let's consider two tables: MY_DB.dbo.MY_TABLE1 that does contain column MY_PROBLEMATIC_COLUMN MY_DB.dbo.MY_TABLE2 that does not contain column MY_PROBLEMATIC_COLUMN As expected, query1: SELECT MY_PROBLEMATIC_COLUMN FROM MY_DB.dbo.MY_TABLE2 Results with: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'MY_PROBLEMATIC_COLUMN'. However, this query2, which uses query1 as a subquery, works: SELECT...

MySQL SQL Select Query with Multiple Sub-Selects

mysql,sql,subquery
OK, so this one has had me ripping my hair out for hours. I feel like there is something obvious I am overlooking. I have 2 tables, service and brand service ------- id brand brand ----- id brandName So service.brand can be any of these: Blank "Other" Integer (matches brand.id)...

“SQL command not properly ended” when using subquery

sql,oracle,oracle11g,subquery
I have the following query working: select a.column_value as artigo from encomenda e, table(e.artigos) a where e.id = 2; This query returns the following output (one row of type menu_t and another of type bebida_menu_t). Note that this isn't simple text, but an object of a type defined by myself,...

MySQL Query - Lowest Values with Multiple Tables / Joins

mysql,join,subquery
i am requesting some help for a query to be used on a custom golf website. what i need is to find the lowest score per player per course. my club has 3 nine hole loops, 27 holes in total, but i want to find the lowest per 9 holes...

MySQL select and count 2 colums with different where clauses

mysql,subquery
I think it should be very easy, but it didn't work at all. I have one table and I'd like to generate a chart which are displaying grouped items and with one where clause (column1) it should display the entries with status 0, and with the other where clause (column2)...

Do not include select columns in group by

sql,postgresql,group-by,subquery
Here are my tables(including only relevant columns) Table: carts address_id - integer Table: addresses name - varchar phone - varchar Table: orders order_number - integer (this is the foreign key for cart table) I want to fetch phone number of the customers who have ordered only once, so I constructed...

Subquery with Sum of top X rows

sql,sql-server,subquery,top-n
I am trying to get the top 5 results for each person from a table. I am able to get the top result for them however I want the sum of the top 5. Select Distinct r.LastName, r.FirstName , r.Class, r.BibNum, (Select top 1 r2.points from Results r2 where r2.season=r.Season...

SQL Select Query between 3 tables

mysql,sql,subquery
I'm having trouble on creating a query to pull data from 3 tables. I have a job_skus table, skus table, and stage table, and I'm trying to select all the job skus for the whole month regardless of jobNo, for example to show a list of all job skus for...

Select Last Record from LEFT JOIN Subquery

mysql,subquery,left-join
I'm playing with Subqueries and I need to display the most recent note from a customers_notes table in a LEFT JOIN. The query I've attempted is returning an error. What am I doing wrong below? SELECT c.*, n.datesubmitted FROM `customers` c LEFT JOIN (SELECT datesubmitted FROM `customers_notes` ORDER BY datesubmitted...

SQL-Server. Calculate formula with subquery in aggregate function

sql,sql-server,tsql,subquery,aggregate-functions
I need to calculate value of Quantity column where Name = A using this formula in SQL-Server: A(Quantity) = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E Sample data: Static Static Dynamic Name ID Quantity -- Here are more columns, but doesn't matter A 1...

Syntax error in Microsoft Access SQL Select query in VBA procedure

sql,ms-access,access-vba,subquery,select-query
I am having following VBA Code that has been giving a syntax error. Can someone please help me figure out what is causing the error? Private Sub Command11_Click() Dim EndingDate As Date 'Getting ending date from Label named endDate EndingDate = endDate StartingDateTxt = DateSerial(Year(EndingDate), Month(EndingDate) - 15, Day(EndingDate)) Dim...

MYSQL Distinct Query on multiple columns

mysql,subquery
I want to select the data from the Food Table on userid but the foodname should be distinct. I have used the following query SELECT * FROM Food WHERE cfid IN ( SELECT distinct(foodname) FROM Food WHERE userid=234 ); but it returns an empty set of data. Food Table cfid...

Mysql: sub-query issue

mysql,count,subquery
I'M NEW TO MYSQL. Problem: Say if I were to count the amount that is contained in the alias table of "x" COUNT(x.xValue) to be 217. Now when I add the sub-query "y" and then do the count again, I have the COUNT(x.xValue) to suddenly square its self -> 47089....

How to limit a subquery in cypher?

neo4j,subquery,cypher
Say I have 3 things in my graph database and each of these 3 things have 0 or more subthings attached to them. How do I make a query to retrieve the top 2 subthings for each of the things in the graph (according to some arbitrary ordering). Here's an...

Using result in the outer query in the inner query

mysql,sql,subquery
What I'm trying to do is to query all the unique district name from the database, and on each district name, get its corresponding MIN and MAX ID. Here's what I have right now: SELECT DISTINCT DISTRICTNAME, (SELECT MIN(`ID`) FROM [DATABASE_NAME] WHERE DISTRICTNAME = DISTRICTNAME) AS 'MIN', (SELECT MAX(`ID`) FROM...

How to count two separate columns in the same table and sum them into a new column

sql,postgresql,join,count,subquery
I have two tables: playernames and matches. SELECT * FROM playernames; id | name ----+------------------ 38 | Abe Lincoln 39 | Richard Nixon 40 | Ronald Reagan (3 rows) SELECT * FROM matches; match_id | winner | loser ----------+--------+------- 6 | 38 | 39 8 | 38 | 39 9...

Error when using WHERE in Correlated Subquery

sql-server,subquery,where-clause,correlated-subquery
I have the following relationship: Bank -> Financing -> Contracts -> Supplier I have to select all the Banks that is related to a Supplier, using the query below: SELECT DISTINCT A.Name AS BankName, A2.Name as SupplierName FROM Companies A INNER JOIN Financing F ON F.IdFinancialCompany = A.Id INNER JOIN...

mysql query retrieve too slow from 10,000 of data (Query Optimizing)

php,join,mysqli,subquery,left-join
10,000 of data in memberships, members and payments table. Retreiving the query is too slow, while searching a particular payment status in each members latest payment. SELECT m.id AS member_id, m.full_name, m.unit, m.street, m.block, m.country, m.postal_code, cat . * , cat.id AS cat_id, mem.membership_num, mem.id AS membership_id FROM memberships mem...

Subquery between three tables using Northwind database with SQL Server

sql,sql-server,subquery,northwind
Table [Orders] : OrderID(Primary Key), CustomerID Table [Order Details] : OrderID(Primary Key), ProductID(Primary Key), Discount Table [Customers] : CustomerID[Primary Key] With these three tables, I want to query productID with highest discount for each CustomerID. I need column for ProductID, CustomerID and Discount. How can I solve this problem?...

Solr subquery merging issue

apache,solr,lucene,subquery
I have an issue to search with SOLR in following scenario, I'd like to get all products within my favorite tag, categories and user. I want all products which created by my favorite user without any filter but products from favorite tag or categories must be filtered with in a...

MySQL implode subquery result and use in WHERE IN

mysql,subquery,where,implode
SELECT name FROM users WHERE id IN ((SELECT manager FROM users_info WHERE id_user = 1)) Here subquery in WHERE IN () is returning one column and row in this format e.g. 12,13 (imploded array if IDs) But whole SELECT returns only first name with id = 12. It should return...

Complex Sql Select statement with latest data

sql,sql-server,select,subquery
I have a process that can be run more than once in a given day or week. I need to figure out the discount amount given on orders that were cancelled. Having the following two tables Transactions TransactionId, Date, Type, OriginalId 1 2015-1-1 Order 2 2015-1-1 Order 3 2015-1-1 Order...

How to optimize or speed up my subquery in mysql or in php

php,mysql,sql,subquery,query-optimization
Sample query: SELECT table1.t1_id,table1.name, table2.address, ( SELECT message FROM table3 WHERE logid = table1.t1_id AND message NOT LIKE "[ SYSTEM%" ORDER BY logs DESC LIMIT 1 ) as message FROM table1 INNER JOIN table2 ON table1.t1_id = table2.t2_id WHERE table1.dateCreated BETWEEN CAST('2015-01-01' as Date) AND CAST('2015-05-30' as Date) ORDER BY...

MySQL SELECT to subquery a many-to-many relationship

php,mysql,select,subquery
I have a bilingual dictionary database that I've created, and the tables are set up like so: lemma (lemmaID, lemma, meaning) collocate (collocateID, lemmaID, collocate, notes, connection) collusage (usageID, lemmaID_u, collocateID_u, japanese, english, englishalt) partofspeech (posID, partofspeech) postolemma (lemmaID_p, posID_p) So far, I have a query that returns tables for...

How to handle no rows returned in an Oracle update using a common sub-select

oracle,null,sql-update,subquery
Consider the update: UPDATE table1 SET c1 = NVL(( SELECT d1 FROM table2 WHERE table1.id = table2.id ), 0), c2 = NVL(( SELECT d2 FROM table2 WHERE table1.id = table2.id ), 0) The NVL function handles the case where the sub-select returns no rows. Is there a good way to...

Update a table based on another table in PostgreSQL

sql,postgresql,subquery
I have two tables: products | id | name | price | type_id ------------------------------------ | 1 | Product A | 500 | 1 | 2 | Product B | 600 | 3 | 3 | Product C | 800 | 15 types | id | price | --------------- | 1...

Get all matching records where IDs are joined to a comma separated list

php,mysql,sql,subquery
I have two database table like below: Form Id Name Fields(varchar-255) FormFields Id Name InputType Sample data for Form Id Name Fields 1 Form1 1,2,3 2 Form2 1,2 Sample data for FormFields Id Name InputType 1 FName TEXT 2 Lname TEXT 3 Email TEXT Now I write query as below:...

SQL: Combine Duplicate Rows And Case Statement Values in Final Data Output

sql,duplicates,subquery,case,distinct
I'm working on a SQL query that counts duplicate records that are based on a text field I am working with: where datasource = 'Web' or 'Internal'. I am currently using a case statement to count the number of times a record shows that value. My question is how do...

SQL Query : Cannot create view due to error #1349 : Help to restructure query

mysql,subquery,mysql-error-1349
I using a query that selects a check type "checks" and looks in the log file to find the most recent log entry referencing this check. SELECT checks.*, logs.last_completed_on, logs.completed_by FROM checks INNER JOIN ( SELECT check_id, MAX(completed_on) AS last_completed_on, completed_by FROM checks_log GROUP BY check_id ) logs ON checks.id...

Is there a more efficient way to execute this nested SQL query?

sql,ms-access,odbc,subquery,correlated-subquery
I am writing a query to fill a select box on a user form and it works fine but when I look at the SQL, I feel like there should be a better, more efficient way to write it. Notice the two nested SELECTs below are from the same table....

SQL query to select the latest records with a distinct subject

sql,sql-server,group-by,subquery,aggregate-functions
I am using SQL Server and have a table set up like below: | id | subject | content | moreContent | modified | | 1 | subj1 | aaaa | aaaaaaaaaaa | 03/03/2015 | | 2 | subj1 | bbbb | aaaaaaaaaaa | 03/05/2015 | | 3 | subj2...

“Faking” a sub-table of results from 3 tables, in SQL

mysql,sql,subquery,nested-lists
If I have a table of cases: CASE_NUMBER | CASE_ID | STATUS | SUBJECT | ---------------------------------------------------------------- 3108 | 123456 | Closed_Billable | Something Interesting 3109 | 325124 | Closed_Billable | Broken printer 3110 | 432432 | Open_Assigned | Email not working And a table of calls: PARENT_ID | STATUS |...

Error in query (1054): Unknown column 'TableValue' in 'where clause'

mysql,sql,join,subquery,where
Following up with SELECTing the contents of the Table, which is a result of another value, I wanna keep a condition here on the generated field. If I execute this query: SELECT *, ( SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType` ) AS `IndexTypeName`, CASE WHEN `IndexType`=1 THEN ( SELECT `Username`...

mysql subquery to sum like checknumbers

mysql,subquery,rollup
I have two tables, one with id and concatenations of fields I'm checking for duplicates, and another with id and all other assorted data, one item being AmountPaid. The below query: Select i.id, i.CheckNumber, AmountPaid from HS i where i.id in ( SELECT id FROM HS_dups WHERE concatckBatch IN (SELECT...

Select Query returns NULL if subquery fails in the from Clause

mysql,sql,subquery
I have two tables trans_transaction and opening_balances. The transactions are to be inserted in the trans_transaction table. I need to fetch the opening_balance, closing_balance from the trans_transaction table. The opening_balances table is used for the first time when there is no transaction for the account in the trans_transaction table. I've...

PostgresSQL database has date field, I want all data but the date column as year only

postgresql,datetime,subquery
I have data that has a date column in the full format but would like the output to only represent the year. eg: from SELECT * FROM tablename; I want something like: SELECT * FROM tablename WITH 'newdatefield' as SELECT EXTRACT(YEAR FROM TIMESTAMP 'datefield'); I'm not great at SQL -...

Retrieving only rows for which a join has a dependancy

mysql,sql,subquery
I am creating a testing system where users are allowed to re-test until they have passed. I would like to get a list, for a given UserID, of tests which are assigned to them which they have scored less than passing (100% for this example) on. I have the following...

trouble with creating subquery

mysql,sql,subquery,mysql-workbench
I am having some trouble with creating a query using subqueries. I have to use subqueries, because it is an assignment from school. I have two tables, an employee one and a department one. From these tables I have to return the employees that earn more than the average salary...

Update only rows where cust_id is present in other table

sql,subquery
I have a query that will present a list of customers to be anonomized: select cu_number into #_t from customer where cu_first_name is not null I want to update last_name to " 'Anonomized' + sequence " and try this: update customer set cu_last_name = 'Anonomized' + convert(varchar, cu_number) where cu_number...

mysql multiple grouping on one table

mysql,group-by,subquery,group-concat
I have one table job_result CREATE TABLE job_result ( node varchar(20) DEFAULT NULL, jobId int(10) DEFAULT NULL, subResult int(1) DEFAULT NULL ) The table contains many nodes. Each node has many jobId. Each jobId has many subResults (usually less than 10). Some example data provided insert into job_result values ('A',...

Optimize MS Access Double SubQuery

sql,ms-access,subquery,ms-access-2010
This MS Access query I'm building is very slow. I am testing only one Month of data (One table driving this query with 28,577 records, 36 columns). Here is what I am trying to accomplish: I want to return the count of unique vehicles by Model that are sold in...

Count rows after specific one

mysql,sql,subquery,left-join
A table users has three columns: id, name, pass. Another table logins has user_id column, an isright boolean (tinyint) column which says whether the login was successful or not and a date column. I need a simple left join to get the user's name and his password (1), the last...

MySQL Two Table Min Value and Group By

mysql,group-by,subquery
I have two tables, products, and products_description Products: Product_ID Product_Price 1 10 2 20 3 10 4 20 5 10 6 20 7 10 8 10 9 10 10 10 Products_Description: Product_ID Product_Name 1 Pants - Black 2 Pants - Black 3 Pants - White 4 Pants - White 5...

Condition in WHERE clause (Oracle)

sql,oracle,subquery
I need a query which returns data based on what month/year it is. Below is a subquery i wrote which returns one row - start_date and end_date are the values i need to use in my main query WITH SUBQ AS (SELECT dim.MONTH_NAME as current_month_name ,dim.year_period as current_month ,dim.PERIOD_YEAR as...

Rails 4, ActiveRecord SQL subqueries

sql,ruby-on-rails-4,subquery,select-n-plus-1
A :parent has_many :children and I am trying to retrieve the age of the oldest child for a parent as an attribute on the parent. I'm open to any solution that accomplishes that efficiently. The reason I'm trying to do a subquery is to let the DB do the n+1...

Subquery returns more than 1 row, but I want it to

mysql,sql,subquery
I'm trying to run a query on my music database. The relevant part of my database looks like this: CREATE TABLE ARTIST ( artistid integer(3) auto_increment not null, name varchar(30) not null, //other columns CONSTRAINT ALBUM_pk PRIMARY KEY (artistid) ); CREATE TABLE SONG ( songid integer(5) auto_increment not null, lineupid...

SQL - Select equal numbers of result of each IN parameter

mysql,sql,subquery
I'm building a quiz game app, and I'd like to improve some queries. When I launch a game, I have to retrieve some random questions from the database. Now, when a game is launched, it has a given category for it, and a given number of data to retrieve. The...

Mysql nested match against not returning any results

php,mysql,select,subquery,match-against
I wanted to select sub sets with match against SELECT * FROM ( SELECT * FROM Movie WHERE MATCH(keywords) AGAINST('$mk') ) WHERE MATCH(genres) AGAINST('$mg') $mk may be "action,thriller" $keywords may be "hero,guns,forest" That doesn't seem to work or i may bee doing something wrong.. Both queries return values. When i...

First query data is used in second query

sql,oracle,subquery,common-table-expression,inline-view
I have a query get_product: select A.product_id, A.name, A.description, A.type_id, B.series_name product_data A inner join series B on A.series_raw_id = B.series_raw_id where A.product_id = 503061 and A.registration_type_id = 4 order by B.series_name and second query select B.series_name, A.TEMPACC_STATUS FROM ACCESS_FACT A inner join **get_product** B on A.TEMPACC_PRODUCT_ID = B.product_id where...

Optimizing a sub-sub-query in MySQL

mysql,optimization,subquery
I have a request ticketing system that allows for commenting on the tickets. I've added a feature that shows the user upon login the most recent comment to any of their tickets as kind of a clue if anything has changed since their last visit. I can accomplish this with...

Why do these queries return differently?

sql,sql-server,sql-update,subquery,isnull
I am working on a query which I am updating a user defined table that always has 1 row (it's part of a much larger operation). This table is populated by querying from a larger employee table based on employee ID. This query is working most of the time but...

MYSQL joining subquery issue

mysql,join,subquery
I have one table that has people's grades in it. +---------+--------+--------+--------+---------------------+ | name | grade1 | grade2 | grade3 | datetime | +---------+--------+--------+--------+---------------------+ | person1 | 50 | 80 | 100 | 2015-05-05 12:00:00 | | person1 | 60 | 43 | 88 | 2015-05-05 12:00:00 | | person1 |...

Laravel - named subquery using Query Builder

php,mysql,laravel,subquery,query-builder
In MySQL subqueries documentation there's an exmaple of subquery: SELECT ... FROM (subquery) [AS] name ... Here's the raw query which I want to transform: select SUBQUERY_NAME.* from (select id, name from items) AS SUBQUERY_NAME Is there any way to do this in Laravel Query Builder without using DB::raw()?...

Mysql count and return just one row of data

mysql,join,count,subquery,having
I need to count the amount of users that have have answered all of those 3 profile_options (so they have at least 3 records in the profile_answers table). SELECT COUNT(DISTINCT(users.id)) users_count FROM users INNER JOIN profile_answers ON profile_answers.user_id = users.id WHERE profile_answers.profile_option_id IN (37,86,102) GROUP BY users.id HAVING COUNT(DISTINCT(profile_answers.id))>=3 The...

Is is possible to have multiple SQL commands within one VALUES entry?

c#,sqlite,subquery
Is is possible to first insert an entry if not exist, and then retrieve the value in one VALUES entry? Something like this: INSERT INTO Sections(data) VALUES( (INSERT OR IGNORE INTO Courses(column) WHERE column='test' SELECT id FROM Courses WHERE column='test') ) So first I am inserting a value if not...

MySQL Subquery related to LEFT JOINS

mysql,subquery,correlated-subquery
I was given the task of extracting customer information from a database and I am stuck on the last part! I hope my explanation is sufficient to describe my problem and attempts. Thank you sincerely for taking the time to even look at this! Goal: Return one row per customer...

Using the division operator in a query that gets data from 3 tables

mysql,database,data,subquery
I am having trouble writing a query that gets information from 3 different tables. Right now I have a query that runs... but I am not getting the right result. From talking to my peers I found out that one way to do this query would be to use the...

SQL subqueries ques :

mysql,sql,subquery
I have these 3 tables : 1)Sailors (sid:INT, sname:VARCHAR(30), rating:INT, age:INT) 2)Boats (bid:INT, bname:VARCHAR(30), color:VARCHAR(10)) 3)Reserves (bid:INT, sid:INT, day:DATE) I don't know how to build a query that displays : The names of the sailors who have reserved at least 2 boats in different colors!...

MySQL group_concat twice in SubQuery DISTINCT

mysql,subquery,group-concat
I have table like this: title name 1 ADAM SMITH 1 JACK SMITH 1 ROBERT ABADI 2 JACK SMITH 2 JAMES ANDERSON 3 JACK SMITH When I call data with query looks like SELECT title, name, group_concat(name) as gname FROM sample GROUP BY title The result is: 1 ADAM SMITH...

Subquery Order by Oracle

sql,oracle,subquery,sql-order-by
My Database has 3 Tables, Device,Postal_Address and Company. Every Device has an Company_ID, and for every company there is an address. The problem is, the old address is still there if i change it, its everytime making a new entry for this. So if i select all devices with address...

Oracle ORA:00904: Subquery in LEFT JOIN

sql,oracle,subquery,correlated-subquery
I'm aware of the subquery limitations of Oracle's ANSI SQL setup. You can't use an identifier in a subquery that is declared more than one level deep. I'm attempting the following query, which as far as I can see is only one level deep, but I'm getting this error. Does...

SQL Solving with Subqueries?

sql,ms-access,subquery
I'm a beginner trying to learn and practice SQL with tables based on this schema: EMPLOYEE - ID, Name ASSIGNMENT - ID, Country, Start, End The primary keys are Employee.ID and all four columns shown for ASSIGNMENT; and ASSIGNMENT.ID is a reference to EMPLOYEE.ID. The domain of start and end...

left join with subquery with one result

sql,tsql,subquery,left-join
Here is a query: SELECT k.[mg_KarId] AS [mg_KarId], k.[SymKar] AS [SymKar], k.[OpiKar] AS [OpiKar], k.[Status] AS [Status], kmlg.SymLok AS Lok_GLS1, kmlk.SymLok AS Lok_KRS1, kmlw.SymLok AS Lok_WLS1 FROM dbo.[mg_vv_Kar_All] AS k WITH (NOLOCK) LEFT JOIN ( SELECT kml.SymKar, kml.SymLok FROM dbo.mg_KarMagLok kml WHERE kml.Mag LIKE 'GLS1' ) kmlg ON k.SymKar =...

Correlated subqueries: How to get the ID of the outer query?

sql,sum,subquery,correlated-subquery
I have multiple tables : Person --------------------- IDPerson, Name StickerTransaction --------------------- IDTransac, IDPerson, IDSomeoneElse, NbStickersReceived, NbStickersGiven Purchase --------------------- IDPurchase, IDPerson, NbStickersBought I'm trying to get every person who never made a transaction or currently have 0 sticker. To have the number of stickers a person has, here's the formula :...

SQL subquery (stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out)

sql,switch-statement,subquery,inner-join,correlated-subquery
I have 2 tables stockin and stockout i want to calculate stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out .. My query is working well but when it doesnt found any record in stock out...

SQL find references with single occurences

sql,group-by,subquery,aggregate
I am trying to find a pure SQL solution for the following problem: If I sell paint and paintbrushes, and keep a record of which colors are sold like this: select OrderNumber, Product, Product_Type, Qty from Sales +-------------+------------+--------------+-----+ | OrderNumber | Product | Product_Type | Qty | +-------------+------------+--------------+-----+ | 0001...

How to joint Two customs Queries with two Joins in only One Query in MySQL

mysql,sql,select,subquery,left-join
The Queries are working perfectly each one separatedly: SELECT asf.surface_name, am.* FROM atp_matchs_to_surfaces m2s LEFT JOIN atp_surfaces asf ON m2s.surfaces_id = asf.surfaces_id LEFT JOIN atp_matchs am ON am.matchs_id = m2s.matchs_id; SELECT att.tournament_type_name, am.* FROM atp_matchs_to_tournament_type m2s LEFT JOIN atp_tournament_type att ON m2s.tournament_type_id = att.tournament_type_id LEFT JOIN atp_matchs am ON am.matchs_id...

Subquery returns more than one row? Can't write in database

subquery
I have a problem with my PHP-Script. I want to insert data into my database but the only thing it returns is an Error. Code: <?php require_once('ConnectionHandler.php'); Class CreateTask { public function createNewTask($description, $subject, $type, $enddate, $priority) { $query = 'INSERT INTO task (description, subject_id, task_type_id, enddate, priority) VALUES (?,...

SQL server Temp table with joins inside other select

sql-server,subquery,union,temp-tables,sql-server-2014-express
I have the following structure: Create @temp Select ...inser...into @temp where ... (select ... from @temp Join tblA where ... ) UNION (Select ... from @temp join tblB where ... ) After build above table I need to be able to perform WHERE, JOINS, ... Something like: Select ... from...

Order by subquery

sql,oracle,subquery,sql-order-by
I have the following oracle SQL code, but I can't understand what is the purpose of ordering by a subquery. Anyone can explain it clearly to me ? SELECT employee_id, last_name FROM employees e ORDER BY ( SELECT department_name FROM departments d WHERE e.department_id = d.department_id ); ...

Selecting from Different Tables, Sub queries or Joins

php,mysql,join,subquery
I have two tables; What I need to do is select comments of a given user. I need cid and heading as results posts pid | heading | body | username 1 smth.... smth.. u1 2 smth.... smth.. u2 posts cid | body | username 1 smth.. u1 2 smth.....

Select multiple value using subquery

sql-server,subquery
i have three tables 1- articles 2- articleTags 3- article_articleTags_Rel articleTags_Rel table has 2 columns 1- ArticleID_FK 2- TagID_FK as you can see the article_articleTags_Rel is to make relation between the articles and articleTags all i want is to select one row from the articles table and and all related...

Declare a BIT variable and set it as a Subquery in SQL Oracle Developer

sql,oracle,variables,boolean,subquery
I am using Oracle SQL Developer and I am trying to set a boolean variable to the result of a subquery. However I am getting a syntax error saying that I am missing the ":". I tried to insert the colon pretty much everywhere in my code but it doesn't...

Selecting Account Numbers with latest date

sql,select,join,subquery
I have been trying to solve this problem now for days. I have table called Stat with the following simplified structure and sample data: Customer BankID AccNumb Type Date Amount AccType Customer 1 Boa 5 Account Statement 2015-01-01 10000,00 Eur Customer 1 CS 10 Account Statement 2015-04-04 22000,00 Eur Customer...

MySQL statement in SQLAlchemy

mysql,sql,sqlalchemy,subquery
How can I write this statement in SQLAlchemy? SELECT post_id, forum_id, topic_id FROM posts p WHERE post_time = (SELECT max(post_time) FROM posts p2 WHERE p2.topic_id = p.topic_id AND p2.forum_id = p.forum_id) GROUP BY forum_id, topic_id, post_id ORDER BY post_time DESC ...

Queries to retreive data in MySQL

mysql,sql,subquery
I have 3 tables. a. salesperson(SSN, lName, startYear, deptNo) b. trip(tripID, SSN, fromCity, toCity, departureDate, returnDate) c. expense(tripID, accountNumber, amount) The primary keys for each table are. SSN for the Salesperson table. TripId for the Trip table, and tripID and account number for the Expense table. The foreign keys for...

WHERE id = (SELECT parent_id FROM sametable LIMIT 1)

mysql,subquery,where-clause
It's basically a radio button behavior but for database. I have table galleries and table images images has image_id, gallery_id and featured columns 1 | 55 | true 2 | 55 | false 3 | 55 | false 4 | 54 | true There should be only one featured image...

using Not in in SubQuery with not equal to

oracle,plsql,subquery
Following two queries are resulting into different number of records by interchanging the condition in following way: select count(1) from clientlist where userid not in (select distinct userid from Clientlist where userid in (select uniqueid from employee e where emplstatus = 'Y' )) Returning 38885 number of records select count(1)...

Four (4) Sub Queries

sql,sql-server,subquery
Is there any possible way to do this kind of sub query: DELETE (INSERT (SELECT (INSERT))) I know how to: INSERT (SELECT): INSERT INTO r (u_id,role) VALUES ((SELECT u_id FROM USER WHERE email="[email protected]"),'Agent'); My problem is: User is present in request table when he verifies his account, system must have...

Text search on many-to-many relationship

mysql,full-text-search,subquery,many-to-many
I have three tables: cars, colours, and cars_has_colours, the first two of which have a many-to-many relationship. I'd like to search cars, ideally using MATCH and AGAINST. This is very simple to achieve if I wanted to search cars.name. I'd like to search colours with MATCH and AGAINST, if possible....