A Select Query Selecting a Select Statement

A select query selecting a select statement

Not sure if Access supports it, but in most engines (including SQL Server) this is called a correlated subquery and works fine:

SELECT  TypesAndBread.Type, TypesAndBread.TBName,
        (
        SELECT  Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM    Sandwiches
        WHERE   (Type = 'Sandwich Type' AND Sandwiches.Type = TypesAndBread.TBName)
                OR (Type = 'Bread' AND Sandwiches.Bread = TypesAndBread.TBName)
        ) As SandwichCount
FROM    TypesAndBread

This can be made more efficient by indexing Type and Bread and distributing the subqueries over the UNION:

SELECT  [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Type = [Sandwiches Types].[Sandwich Type]
        )
FROM    [Sandwiches Types]
UNION ALL
SELECT  [Breads].[Bread] As TBName, "Bread" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Bread = [Breads].[Bread]
        )
FROM    [Breads]

How to write a select statement inside another select in SQL

looks like you are missing a comma after MAX(h.edition)

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition), 
          (SELECT h.preview, h.file WHERE h.idtitle_history = MAX(h.idtitle_history))
FROM mc_boxes_has_titles b
LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
WHERE b.mc_boxes_idmc_boxes = 12
     AND h.edition IS NOT NULL
GROUP BY b.mc_boxes_idmc_boxes, idtitles
ORDER BY b.sortorder;

besides the comma, you are selecting two fields in your subquery

SELECT b.mc_boxes_idmc_boxes,
          t.idtitles, 
          t.title,
          t.languages_idlanguages,
          MAX(h.idtitle_history),
          MAX(h.edition), 
          (SELECT preview FROM title_history WHERE idtitle_history = MAX(h.idtitle_history)),
          (SELECT [file] FROM title_history WHERE idtitle_history = MAX(h.idtitle_history))
FROM mc_boxes_has_titles b
LEFT JOIN titles t ON b.titles_idtitles = t.idtitles
LEFT JOIN title_history h ON h.titles_idtitles = t.idtitles
WHERE b.mc_boxes_idmc_boxes = 12
     AND h.edition IS NOT NULL
GROUP BY b.mc_boxes_idmc_boxes, idtitles
ORDER BY b.sortorder;

SQL: Two select statements in one query

You can do something like this:

 (SELECT
    name, games, goals
    FROM tblMadrid WHERE name = 'ronaldo')
 UNION
 (SELECT
    name, games, goals
    FROM tblBarcelona WHERE name = 'messi')
ORDER BY goals;

See, for example: https://dev.mysql.com/doc/refman/5.0/en/union.html

Nested select statement in SQL Server

You need to alias the subquery.

SELECT name FROM (SELECT name FROM agentinformation) a  

or to be more explicit

SELECT a.name FROM (SELECT name FROM agentinformation) a  

Use Select data in nested Select statement in MySQL

In the innermost subquery you are referencing a column from the outer most query. This is not permitted for subqueries in the FROM clause (derived tables). However - You don't need that subquery. What you need is COUNT(DISTINCT device_date)

Rewrite

(SELECT 
    COUNT(*) 
  FROM
    (SELECT 
      * 
    FROM
      `attendance_master` 
    WHERE `delete_flag` = 'F' 
      AND login_id = 'vinod.kumbala' 
      AND `tour_sub_code` = tc.`tour_sub_code` 
    GROUP BY `device_date`) t1) AS newNoOfdays 

to

(SELECT 
    COUNT(DISTINCT device_date)
  FROM `attendance_master` 
  WHERE `delete_flag` = 'F' 
    AND login_id = 'vinod.kumbala' 
    AND `tour_sub_code` = tc.`tour_sub_code` 
) AS newNoOfdays 

You can also rewrite the full query to a LEFT JOIN query:

SELECT 
  tc.expense AS expense,
  tc.tour_sub_code,
  tc.login_id,
  COUNT(DISTINCT device_date) AS newNoOfdays 
FROM tc_wallet tc
LEFT JOIN attendance_master am
  ON  am.tour_sub_code = tc.tour_sub_code
  AND am.delete_flag = 'F' 
  AND am.login_id = 'vinod.kumbala' 
WHERE tc.login_id = 'vinod.kumbala' 
  AND tc.expense = 'Daily Allowance' 
  AND tc.delete_flag = 'F' 
  AND tc.status != 'reject' 

How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

You need to identify the primary key in TableA in order to delete the correct record. The primary key may be a single column or a combination of several columns that uniquely identifies a row in the table. If there is no primary key, then the ROWID pseudo column may be used as the primary key.

DELETE FROM tableA
WHERE ROWID IN 
  ( SELECT q.ROWID
    FROM tableA q
      INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%')
      AND (u.FldFormat = 'Date'));

How to SELECT based on value of another SELECT

You can calculate the total (and from that the desired percentage) by using a subquery in the FROM clause:

SELECT Name,
       SUM(Value) AS "SUM(VALUE)",
       SUM(Value) / totals.total AS "% of Total"
FROM   table1,
       (
           SELECT Name,
                  SUM(Value) AS total
           FROM   table1
           GROUP BY Name
       ) AS totals
WHERE  table1.Name = totals.Name
AND    Year BETWEEN 2000 AND 2001
GROUP BY Name;

Note that the subquery does not have the WHERE clause filtering the years.

Selecting from another select statements results

Use a JOIN rather than 2 queries.

SELECT p.picid, p.description, r.relatedseries 
FROM product_series p
INNER JOIN RelatedItems r ON p.newseries = r.relatedseries
    AND r.series = @getseries 

Get the data that you need in as few passes as you can manage.

NOTE: This may give you duplicates of p.picid and p.description if you have multiple records in RelatedItems that match up to product_series.

---

Related Topics