I am an old-school MySQL user and have always preferred `JOIN` over sub-query. But nowadays everyone uses sub-query, and I hate it; I don&#39;t know why.   

I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a `JOIN` and therefore is there nothing to worry about?


Join vs. sub-query

I have a table `story_category` in my database with corrupt entries. The next query returns the corrupt entries:

    SELECT * 
    FROM  story_category 
    WHERE category_id NOT IN (
        SELECT DISTINCT category.id 
        FROM category INNER JOIN 
           story_category ON category_id=category.id);

I tried to delete them executing:

    DELETE FROM story_category 
    WHERE category_id NOT IN (
        SELECT DISTINCT category.id 
        FROM category 
          INNER JOIN story_category ON category_id=category.id);

But I get the next error: 

&gt; \#1093 - You can&#39;t specify target table &#39;story_category&#39; for update in FROM clause 

How can I overcome this?


MySQL Error 1093 - Can&#39;t specify target table for update in FROM clause

Why doesn&#39;t the following work?

    SELECT name FROM (SELECT name FROM agentinformation)

I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as  

    SELECT name FROM agentinformation

Doesn&#39;t the inner select statement create a result set which the outer SELECT statement then queries?

Nested select statement in SQL Server

I am running this query on MySQL

    SELECT ID FROM (
        SELECT ID, msisdn
        FROM (
            SELECT * FROM TT2
        )
    );

and it is giving this error:

&gt; Every derived table must have its own alias. 

What&#39;s causing this error?

What is the error &quot;Every derived table must have its own alias&quot; in MySQL?

Using postgres 8.4,  My goal is to update existing table:

    CREATE TABLE public.dummy
    (
      address_id SERIAL,
      addr1 character(40),
      addr2 character(40),
      city character(25),
      state character(2),
      zip character(5),
      customer boolean,
      supplier boolean,
      partner boolean
      
    )
    WITH (
      OIDS=FALSE
    );

Initially i tested my query using insert statement:

    insert into address customer,supplier,partner
    SELECT  
    	case when cust.addr1 is not null then TRUE else FALSE end customer, 
    	case when suppl.addr1 is not null then TRUE else FALSE end supplier,
    	case when partn.addr1 is not null then TRUE else FALSE end partner
    from (
    	SELECT *
    		from address) pa
    	left outer join cust_original cust
    		on (pa.addr1=cust.addr1 and pa.addr2=cust.addr2 and pa.city=cust.city 
    			and pa.state=cust.state and substring(cust.zip,1,5) = pa.zip  )
    	left outer join supp_original suppl 
    		on (pa.addr1=suppl.addr1 and pa.addr2=suppl.addr2 and pa.city=suppl.city 
    			    and pa.state=suppl.state and pa.zip = substring(suppl.zip,1,5))
    	left outer join partner_original partn
    		on (pa.addr1=partn.addr1 and pa.addr2=partn.addr2 and pa.city=partn.city
    			      and pa.state=partn.state and pa.zip = substring(partn.zip,1,5) )
    where pa.address_id = address_id

being Newbie I&#39;m failing converting to update statement ie., updating existing rows with values returned by select statement.
Any help is highly appreciated.

updating table rows in postgres using subquery

I have a table with the following fields:

    id (Unique)
    url (Unique)
    title
    company
    site_id

Now, I need  to remove rows having same `title, company and site_id`. One way to do it will be using the following SQL along with a script (**`PHP`**):

    SELECT title, site_id, location, id, count( * ) 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count( * ) &gt;1

After running this query, I can remove duplicates using a server side script.

But, I want to know if this can be done only using SQL query.

 

Remove duplicate rows in MySQL

In this excellent [SO question][1], differences between `CTE` and `sub-queries` were discussed.

I would like to specifically ask: 

In what circumstance is each of the following more efficient/faster?

- CTE
- Sub-Query
- Temporary Table
- Table Variable

Traditionally, I&#39;ve used lots of `temp tables` in developing `stored procedures` - as they seem more readable than lots of intertwined sub-queries.

`Non-recursive CTE`s encapsulate sets of data very well, and are very readable, but are there specific circumstances where one can say they will always perform better? or is it a case of having to always fiddle around with the different options to find the most efficient solution?
 

----------

**EDIT**  

I&#39;ve recently been told that in terms of efficiency, temporary tables are a good first choice as they have an associated histogram i.e. statistics.


  [1]: https://stackoverflow.com/questions/706972/difference-between-cte-and-subquery

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

Since Postgres came out with the ability to do `LATERAL` joins, I&#39;ve been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more.

I understand that `LATERAL` joins may be able to help me, but even after reading articles like [this one](http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/) from Heap Analytics, I still don&#39;t quite follow.

What is the use case for a `LATERAL` join? What is the difference between a `LATERAL` join and a subquery?

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

From this post [How to use ROW_NUMBER in the following procedure?][1]

There are two versions of answers where one uses a `sub-query` and the other uses a `CTE` to solve the same problem.

Now then, what is the advantage of using a `CTE (Common Table Expression)` over a &#39;sub-query`(thus, more ***readable*** what the query is actually doing)

The only advantage of using a `CTE` over `sub-select` is that I can actually *name* the `sub-query`.  Are there any other differences between those two *when a CTE is used as a simple (non-recursive) CTE?*


  [1]: https://stackoverflow.com/questions/706720/how-to-use-rownumber-in-the-following-procedure/706756#706756

Difference between CTE and SubQuery?

How can I make this query in Laravel:

    SELECT 
    	`p`.`id`,
    	`p`.`name`, 
    	`p`.`img`, 
    	`p`.`safe_name`, 
    	`p`.`sku`, 
    	`p`.`productstatusid` 
    FROM `products` p 
    WHERE `p`.`id` IN (
    	SELECT 
    		`product_id` 
    	FROM `product_category`
        WHERE `category_id` IN (&#39;223&#39;, &#39;15&#39;)
    )
    AND `p`.`active`=1

I could also do this with a join, but I need this format for performance.

How to do this in Laravel, subquery where in

I&#39;ve got a couple of duplicates in a database that I want to inspect, so what I did to see which are duplicates, I did this:

    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) &gt; 1

This way, I will get all rows with relevant_field occuring more than once. This query takes milliseconds to execute.

Now, I wanted to inspect each of the duplicates, so I thought I could SELECT each row in some_table with a relevant_field in the above query, so I did like this:

    SELECT *
	FROM some_table 
    WHERE relevant_field IN
	(
		SELECT relevant_field
		FROM some_table
		GROUP BY relevant_field
		HAVING COUNT(*) &gt; 1
	)


This turns out to be extreeeemely slow for some reason (it takes minutes). What exactly is going on here to make it that slow? relevant_field is indexed.

Eventually I tried creating a view &quot;temp_view&quot; from the first query `(SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) &gt; 1)`, and then making my second query like this instead:

    SELECT *
    FROM some_table
    WHERE relevant_field IN
    (
    	SELECT relevant_field
    	FROM temp_view
    )

And that works just fine. MySQL does this in some milliseconds.

Any SQL experts here who can explain what&#39;s going on?


MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

I wish to know if I have a **join** query something like this - 

    Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

and a **subquery** something like this -

    Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

When I consider **performance** which of the two queries would be faster and *why* ?

 Also is there a time when I should prefer one over the other?

Sorry if this is too trivial and asked before but I am confused about it. Also, it would be great if you guys can suggest me **tools** i should use to measure performance of two queries. Thanks a lot!


SQL Joins Vs SQL Subqueries (Performance)?

I really would appreciate your help.

Probably it&#39;s a quite simple problem to solve - but I&#39;m not the one .. ;-)

I have two tables in SQL Server:

 1. article
 2. prices

Now I want to select a certain set of ids and insert some entries into the prices-table with those ID.

e.g. (wrong and not working SQL)

    INSERT INTO prices (group, id, price) 
    VALUES (7, (select articleId from article WHERE name LIKE &#39;ABC%&#39;), 1.50);

SQL Error -&gt; subquery has more than 1 value

thanks for help

How can I insert values into a table, using a subquery with more than one result?

I have this query I have written in PostgreSQL that returns an error saying:

&gt; [Err] ERROR:  
&gt; LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge

This is the whole query:

    SELECT COUNT (made_only_recharge) AS made_only_recharge
    FROM (
        SELECT DISTINCT (identifiant) AS made_only_recharge
        FROM cdr_data
        WHERE CALLEDNUMBER = &#39;0130&#39;
        EXCEPT
        SELECT DISTINCT (identifiant) AS made_only_recharge
        FROM cdr_data
        WHERE CALLEDNUMBER != &#39;0130&#39;
    )

I have a similar query in Oracle that works fine. The only change is where I have `EXCEPT` in Oracle I have replaced it with the `MINUS` key word. I am new to Postgres and don&#39;t know what it is asking for. What&#39;s the correct way of handling this?

subquery in FROM must have an alias

I&#39;m running this SQL query:

    SELECT wp_woocommerce_order_items.order_id As No_Commande
    FROM  wp_woocommerce_order_items
    LEFT JOIN 
    	(
    		SELECT meta_value As Prenom
    		FROM wp_postmeta
    		WHERE meta_key = &#39;_shipping_first_name&#39;
    	) AS a
    ON wp_woocommerce_order_items.order_id = a.post_id
    WHERE  wp_woocommerce_order_items.order_id =2198

And I get this error:

&gt;  #1054 - Unknown column &#39;a.post_id&#39; in &#39;on clause&#39;.

I think my code is pretty simple, but I can&#39;t make it right. What am I doing wrong? 

SQL LEFT JOIN Subquery Alias

I am trying to write the following query on postgresql:

	select name, author_id, count(1), 
		(select count(1)
		from names as n2
		where n2.id = n1.id
			and t2.author_id = t1.author_id
		)				
	from names as n1
	group by name, author_id

This would certainly work on Microsoft SQL Server but it does not at all on postegresql. I read its documentation a bit and it seems I could rewrite it as:

	select name, author_id, count(1), total						
	from names as n1, (select count(1) as total
		from names as n2
		where n2.id = n1.id
			and n2.author_id = t1.author_id
		) as total
	group by name, author_id

But that returns the following error on postegresql: &quot;subquery in FROM cannot refer to other relations of same query level&quot;. So I&#39;m stuck. Does anyone know how I can achieve that?

Thanks

How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

I am trying to do a query like this:

    DELETE FROM term_hierarchy AS th
    WHERE th.parent = 1015 AND th.tid IN (
        SELECT DISTINCT(th1.tid)
        FROM term_hierarchy AS th1
        INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
        WHERE th1.parent = 1015
    );

As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;AS th
    WHERE th.parent = 1015 AND th.tid IN (
      SELECT DISTINCT(th1.tid)
      FROM ter&#39; at line 1


I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what&#39;s wrong here?

**Update**: As answered below, MySQL does not allow the table you&#39;re deleting from be used in a subquery for the condition.

MySQL DELETE FROM with subquery as condition

I&#39;m trying to execute this query:

    SELECT mac, creation_date 
    FROM logs 
    WHERE logs_type_id=11
    AND mac NOT IN (select consols.mac from consols)

But I get no results. I tested it, and I know that there is something wrong with the syntax. In MySQL such a query works perfectly. I&#39;ve added a row to be sure that there is one `mac` which does not exist in the `consols` table, but still it isn&#39;t giving any results.


PostgreSQL &#39;NOT IN&#39; and subquery

This code doesn&#39;t work for MySQL 5.0, how to re-write it to make it work

    DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) &gt; 1 ))

I want to delete columns that dont have unique id. I will add that most of the time its only one id(I tried the in syntax and it doesnt work as well).

How to delete from select in MySQL?

How to do that?

Former title of this question was &quot;*using rank (@Rank := @Rank + 1) in complex query with subqueries - will it work?*&quot; because I was looking for solution using ranks, but now I see that the solution posted by Bill is much much better.

Original question:

I&#39;m trying to compose a query that would take last record from each group given some defined order:

    SET @Rank=0;

    select s.*
    from (select GroupId, max(Rank) AS MaxRank
          from (select GroupId, @Rank := @Rank + 1 AS Rank 
                from Table
                order by OrderField
                ) as t
          group by GroupId) as t 
      join (
          select *, @Rank := @Rank + 1 AS Rank
          from Table
          order by OrderField
          ) as s 
      on t.GroupId = s.GroupId and t.MaxRank = s.Rank
    order by OrderField

Expression `@Rank := @Rank + 1` is normally used for rank, but for me it looks suspicious when used in 2 subqueries, but initialized only once. Will it work this way?

And second, will it work with one subquery that is evaluated multiple times? Like subquery in where (or having) clause (another way how to write the above):

    SET @Rank=0;

    select Table.*, @Rank := @Rank + 1 AS Rank
    from Table
    having Rank = (select max(Rank) AS MaxRank
                  from (select GroupId, @Rank := @Rank + 1 AS Rank 
                        from Table as t0
                        order by OrderField
                        ) as t
                  where t.GroupId = table.GroupId
                 )
    order by OrderField

Thanks in advance!

Get records with highest/smallest &lt;whatever&gt; per group

With SQL I can easily do sub-queries like this

    User.where(:id =&gt; Account.where(..).select(:user_id))

This produces:

    SELECT * FROM users WHERE id IN (SELECT user_id FROM accounts WHERE ..)

How can I do this using rails&#39; 3 activerecord/ arel/ meta_where? 

I do need/ want real subqueries, no ruby workarounds (using several queries).


Subqueries in activerecord

Is there any SQL subquery syntax that lets you define, literally, a temporary table?

For example, something like

    SELECT
      MAX(count) AS max,
      COUNT(*) AS count
    FROM
      (
        (1 AS id, 7 AS count),
        (2, 6),
        (3, 13),
        (4, 12),
        (5, 9)
      ) AS mytable
      INNER JOIN someothertable ON someothertable.id=mytable.id

This would save having to do two or three queries: creating temporary table, putting data in it, then using it in a join.

I am using MySQL but would be interested in other databases that could do something like that.

Can you define &quot;literal&quot; tables in SQL?

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

  

      INSERT INTO Results
        (
         People,
         names,
        )
        VALUES
        (
         (
           SELECT d.id
           FROM Names f
           JOIN People d ON d.id  = f.id
         ),
        
         (
          &quot;Henry&quot;
         ),
        );

I **WANT** to populate the new table with all results returning from this subquery. How do I do this without getting a **ERROR 1242 (21000): Subquery returns more than 1 row**


MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

I need to do a big query, but I only want the latest records.

For a single entry I would probably do something like

    SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1;

But I need to pull the latest records for a large (thousands of entries) number of records, but only the latest entry.

Here&#39;s what I have.  It&#39;s not very efficient.  I was wondering if there&#39;s a better way.

    SELECT * FROM table a WHERE ID IN $LIST AND date = (SELECT max(date) FROM table b WHERE b.id = a.id);

Efficient latest record query with Postgresql

I&#39;m creating an SQL statement that will return a month by month summary on sales.

The summary will list some simple columns for the date, total number of sales and the total value of sales.

However, in addition to these columns, i&#39;d like to include 3 more that will list the months best customer by amount spent. For these columns, I need some kind of inline subquery that can return their ID, Name and the Amount they spent.

My current effort uses an inline `SELECT` statement, however, from my knowledge on how to implement these, you can only return one column and row per in-line statement. 

To get around this with my scenario, I can of course create 3 separate in-line statements, however, besides this seeming impractical, it increases the query time more that necessary.

    SELECT	
    	DATE_FORMAT(OrderDate,&#39;%M %Y&#39;) AS OrderMonth,
    	COUNT(OrderID) AS TotalOrders, 
    	SUM(OrderTotal) AS TotalAmount, 
    
    	(SELECT SUM(OrderTotal) FROM Orders WHERE DATE_FORMAT(OrderDate,&#39;%M %Y&#39;) = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS TotalCustomerAmount,

    	(SELECT OrderCustomerFK FROM Orders WHERE DATE_FORMAT(OrderDate,&#39;%M %Y&#39;) = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerID,

    	(SELECT CustomerName FROM Orders INNER JOIN Customers ON OrderCustomerFK = CustomerID WHERE DATE_FORMAT(OrderDate,&#39;%M %Y&#39;) = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerName
   
    FROM Orders     
    GROUP BY DATE_FORMAT(OrderDate,&#39;%m%y&#39;)
    ORDER BY DATE_FORMAT(OrderDate,&#39;%y%m&#39;) DESC

How can i better structure this query?


----------
## FULL ANSWER ##

After some tweaking of Dave Barkers solution, I have a final version for anyone in the future looking for help.

The solution by Dave Barker worked perfectly with the customer details, however, it made the simpler Total Sales and Total Sale Amount columns get some crazy figures.

   

    SELECT  
        	Y.OrderMonth, 	Y.TotalOrders,	Y.TotalAmount,
        	Z.OrdCustFK,  Z.CustCompany, 	Z.CustOrdTotal, Z.CustSalesTotal   

     
     FROM 
    		(SELECT
    			OrdDate,
    			DATE_FORMAT(OrdDate,&#39;%M %Y&#39;) AS OrderMonth, 
    			COUNT(OrderID) AS TotalOrders, 
    			SUM(OrdGrandTotal) AS TotalAmount
    			FROM Orders
    			WHERE OrdConfirmed = 1    
    			GROUP BY DATE_FORMAT(OrdDate,&#39;%m%y&#39;) 
    			ORDER BY DATE_FORMAT(OrdDate,&#39;%Y%m&#39;) DESC)
    	Y INNER JOIN 
    		(SELECT 
    			DATE_FORMAT(OrdDate,&#39;%M %Y&#39;) AS CustMonth, 
    			OrdCustFK, 
    			CustCompany, 
    			COUNT(OrderID) AS CustOrdTotal,
    			SUM(OrdGrandTotal) AS CustSalesTotal 
    		FROM Orders INNER JOIN CustomerDetails ON OrdCustFK = CustomerID
    		WHERE OrdConfirmed = 1
    		GROUP BY DATE_FORMAT(OrdDate,&#39;%m%y&#39;), OrdCustFK 
    		ORDER BY SUM(OrdGrandTotal) DESC) 
    	Z ON Z.CustMonth = Y.OrderMonth
    
    GROUP BY DATE_FORMAT(OrdDate,&#39;%Y%m&#39;)
    ORDER BY DATE_FORMAT(OrdDate,&#39;%Y%m&#39;) DESC




MySQL: Returning multiple columns from an in-line subquery

I am writing this query to find duplicate CTN Records in table1. So my thinking is if the CTN_NO appears more than twice or higher , I want it shown in my SELECT * statement output on top.

I tried the following sub-query logic but I need pulls 

      SELECT *
             table1 
       WHERE S_IND=&#39;Y&#39;
         and CTN_NO = (select CTN_NO 
                         from table1 
                        where S_IND=&#39;Y&#39; 
                          and count(CTN_NO) &lt; 2);
    order by 2

Find duplicate entries in a column

I have tried to write a query statement with a subquery and an `IN` expression for many times. But I have never succeeded.

I always get the exception, &quot; Syntax error near keyword &#39;IN&#39; &quot;, the query statement was build like this,

    SELECT t0.ID, t0.NAME
    FROM EMPLOYEE t0
    WHERE IN (SELECT ?
              FROM PROJECT t2, EMPLOYEE t1
              WHERE ((t2.NAME = ?) AND (t1.ID = t2.project)))

I know the word before &#39;IN&#39; lose.

Have you ever written such a query? Any suggestion?

JPA 2.0, Criteria API, Subqueries, In Expressions

[I posted a related question](https://stackoverflow.com/q/7922211/902825), but this is another part of my puzzle.

I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any other extra, non -SQL/-query entities).

I have a query like this:

       UPDATE my_table
          SET processing_by = our_id_info  -- unique to this worker
        WHERE trans_nbr IN (
                            SELECT trans_nbr
                              FROM my_table
                             GROUP BY trans_nbr
                            HAVING COUNT(trans_nbr) &gt; 1
                             LIMIT our_limit_to_have_single_process_grab
                           )
    RETURNING row_id;

If I could do `FOR UPDATE ON my_table` at the end of the subquery, that&#39;d be divine (and fix my other question/problem). But that won&#39;t work: can&#39;t combine this with `GROUP BY` (which is necessary for figuring out the count). Then I could just take those trans_nbr&#39;s and do a query first to get the (soon-to-be-) former `processing_by` values.

I&#39;ve tried doing like:

       UPDATE my_table
          SET processing_by = our_id_info -- unique to this worker
         FROM my_table old_my_table
         JOIN (
                 SELECT trans_nbr
                   FROM my_table
               GROUP BY trans_nbr
                 HAVING COUNT(trans_nbr) &gt; 1
                  LIMIT our_limit_to_have_single_process_grab
              ) sub_my_table
           ON old_my_table.trans_nbr = sub_my_table.trans_nbr
        WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
          AND my_table.processing_by = old_my_table.processing_by
    RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

But that can&#39;t work; `old_my_table` is not visible outside the join; the `RETURNING` clause is blind to it.

I&#39;ve long since lost count of all the attempts I&#39;ve made; I have been researching this for literally hours.

If I could just find a bullet-proof way to lock the rows in my subquery - and ONLY those rows, and WHEN the subquery happens - all the concurrency issues I&#39;m trying to avoid would disappear ...

---

**UPDATE:** I had a typo in the non-generic code of the above. I retried after Erwin Brandstetter suggested it should work. Since it took me so long to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now...  :&gt;

What I now have (that works) is like this:

       UPDATE my_table
          SET processing_by = our_id_info -- unique to this worker
         FROM my_table AS old_my_table
        WHERE trans_nbr IN (
                              SELECT trans_nbr
                                FROM my_table
                            GROUP BY trans_nbr
                              HAVING COUNT(*) &gt; 1
                               LIMIT our_limit_to_have_single_process_grab
                           )
          AND my_table.row_id = old_my_table.row_id
    RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

The `COUNT(*)` is per a suggestion from **Flimzy** in a comment on my other (linked above) question.

[Please see my other question](https://stackoverflow.com/q/7922211/902825) for correctly implementing concurrency and even a non-blocking version; THIS query merely shows how to get the old and new values from an update, ignore the bad/wrong concurrency bits.

Return pre-UPDATE column values using SQL only

I&#39;ve just recently started using SQLAlchemy and am still having trouble wrapping my head around some of the concepts.

Boiled down to the essential elements, I have two tables like this (this is through Flask-SQLAlchemy):

    class User(db.Model):
        __tablename__ = &#39;users&#39;
        user_id = db.Column(db.Integer, primary_key=True)

    class Posts(db.Model):
        __tablename__ = &#39;posts&#39;
        post_id = db.Column(db.Integer, primary_key=True)
        user_id = db.Column(db.Integer, db.ForeignKey(&#39;users.user_id&#39;))
        post_time = db.Column(db.DateTime)

        user = db.relationship(&#39;User&#39;, backref=&#39;posts&#39;)

How would I go about querying for a list of users and their newest post (excluding users with no posts). If I was using SQL, I would do:

    SELECT [whatever]
    FROM posts AS p
        LEFT JOIN users AS u ON u.user_id = p.user_id
    WHERE p.post_time = (SELECT MAX(post_time) FROM posts WHERE user_id = u.user_id)

So I know exactly the &quot;desired&quot; SQL to get the effect I want, but no idea how to express it &quot;properly&quot; in SQLAlchemy.

Edit: in case it&#39;s important, I&#39;m on SQLAlchemy 0.6.6.

SQLAlchemy - subquery in a WHERE clause

**How do I specify the parent query field from within a subquery in MySQL?**

For Example:  
I have written a basic Bulletin Board type program in PHP.

In the database each post contains: id(PK) and parent_id(the id of the parent post).  If the post is itself a parent, then its parent_id is set to 0.

I am trying to write a mySQL query that will find every parent post and the number of children that the parent has.

    $query = &quot;SELECT id, (
          SELECT COUNT(1) 
          FROM post_table 
          WHERE parent_id = id
    ) as num_children
    FROM post_table
    WHERE parent_id = 0&quot;;

The tricky part is that the first **id** doesn&#39;t know that it should be referring to the second *id* that is outside of the subquery.  I know that I can do SELECT id AS id_tmp  and then refer to it inside the subquery, but then if I want to also return the id and keep &quot;id&quot; as the column name, then I&#39;d have to do a query that returns me 2 columns with the same data (which seems messy to me)

    $query = &quot;SELECT id, id AS id_tmp, 
                (SELECT COUNT(1)
                FROM post_table
                WHERE parent_id = id_tmp) as num_children
             FROM post_table
             WHERE parent_id = 0&quot;;

The messy way works fine, but I feel an opportunity to learn something here so I thought I&#39;d post the question.

How to specify the parent query field from within a subquery in MySQL?

I have this Statement:

    INSERT INTO qa_costpriceslog (item_code, invoice_code, item_costprice)
        VALUES (1, 2, (SELECT item_costprice FROM qa_items WHERE item_code = 1));

I&#39;m trying to insert a value copy the same data of item_costprice, but show me the error:

    Error Code: 1136. Column count doesn&#39;t match value count at row 1

How i can solve this?

INSERT INTO with SubQuery MySQL

I have two separate databases. I am trying to update a column in one database to the values of a column from the other database:


    UPDATE customer
    SET customer_id=
       (SELECT t1 FROM dblink(&#39;port=5432, dbname=SERVER1 user=postgres password=309245&#39;,
       &#39;SELECT store_key FROM store&#39;) AS (t1 integer));

This is the error I am receiving:

&gt;     ERROR:  more than one row returned by a subquery used as an expression


Any ideas?

Postgres Error: More than one row returned by a subquery used as an expression

Example:

    SELECT
       (SELECT SUM(...) FROM ...) as turnover,
       (SELECT SUM(...) FROM ...) as cost,
       turnover - cost as profit

Sure this is invalid (at least in Postgres) but how to achieve the same in a query without rewriting the sub-query twice?

How to reuse a result column in an expression for another result column

Is it possible to use subqueries within alter expressions in PostgreSQL?

I want to alter a sequence value based on a primary key column value.

I tried using the following expression, but it wouldn&#39;t execute.

    alter sequence public.sequenceX restart with (select max(table_id)+1 from table)

Postgresql - Using subqueries with alter sequence expressions

I see many similar questions but they&#39;re either so complex I can&#39;t understand them, or they don&#39;t seem to be asking the same thing.

It&#39;s simple: I have two columns: users (dmid) and downloads (dfid).

1. Select all users who downloaded a specific file:

        SELECT DISTINCT dmid FROM downloads_downloads where dfid = &quot;7024&quot;

2. Using the users above, find all the files they all downloaded:

        SELECT dfid from downloads_downloads WHERE dmid = {user ids from #1 above}

3. Count and order the dfid results , so we can see how many downloads each file received:

        dfid    dl_count_field
        ----    --------------
        18       103
        3        77
        903      66

 
**My attempt at answering.**

This seems close, but MySql bogs down and doesn&#39;t respond even after 30 seconds--I restart Apache eventually. And I do not now how to structure the count and order by without getting syntax errors because of the complex statement--and it may not even be the right statement.

    SELECT dfid from downloads_downloads WHERE dmid IN (
        SELECT DISTINCT dmid FROM `downloads_downloads` where dfid = &quot;7024&quot;)


All Subquery Solutions on Gang of Coders

Total of 35 Subquery Solutions