![]() ![]() The database has two tables representing the production statistics of a fictional apple farm company named EverRed. The dataīefore getting to the examples, let’s briefly look at the sample database we will use. In this article, I'll show examples of both cases: when a subquery is a must and when a subquery should be avoided and replaced by a JOIN. But of course, in some cases, using a subquery is the only way to solve a data question. If you can avoid a subquery and replace it with a JOIN clause, you should do so without hesitation. To simplify the query I have created 2 views CREATE VIEW UsedVIPĪnd the final query is: SELECT INET_NTOA(MIN(INET_ATON(a.However, in some cases a subquery can be replaced with a more efficient JOIN. If this is likely to happen I think the best way to get around this would be to only insert user names from one domain at any one time. If your NewUsers table contained these records: ('jim',''),īoth your query and mine would allocate all of these to VIP 100.50.20.5. But you can get the next VIP using this: SELECT INET_NTOA(MAX(INET_ATON(UserMap.VIP)) + 1) AS NextVIPĪnother issue with your problem is collisions in NewUsers, e.g. ![]() I am not sure what your logic is for handling cases where there is no VIP available so can't really suggest a solution to this part. To get around this you can use a UNION: SELECT INET_NTOA(MIN(INET_ATON(a.VIP))) AS VIP, ![]() Whereas the query I have written will only return the rows where VIP is not null: VIP USER DOMAIN if 100.50.20.5 and 100.50.20.1 are removed from UserMap your query would return: VIP USER DOMAIN The query above will not return rows where there is no VIP available, e.g. I am not sure how much, if any, more efficient this will be, but the query can be rewritten without nesting multiple subqueries: SELECT INET_NTOA(MIN(INET_ATON(UserMap.VIP))) AS VIP, My question is: Is there a way to reference a column from an outer query inside of a join on an inner query? If not, what kind of (if any) alternatives exist without nesting subqueries in an inefficient manner?Īgain, I have a fiddle here: !2/39c51/2/0 While the logic of the query itself makes sense, because replacing the outer query reference with a string constant it would represent works fine: mysql> select I'm trying to make this more efficient by using joins instead of nested subqueries, so I replaced the inner query with a join and listed the outer query's columns in the ON clause, but it seems this isn't possible: mysql> selectĮRROR 1054 (42S22): Unknown column 'n.domain' in 'on clause' I can do this with a subquery inside a correlated subquery, like so: mysql> selectīut that is horrendously inefficient, and my production mapping and newusers tables are 300k and 50k rows respectively, so this is out of the question. The result I'm looking for would look like: +-+-+-+ 1 because of their respective user conflicts (or lack thereof). ![]() 5 because thats the earliest IP which has neither a 'jim' nor a 'sally', and then to. The idea here is to assign all users under to. I have another table, which contains the yet-to-be assigned users, again, a small scale example: mysql> select * from test.newusers An example on a smaller scale would look like the following: mysql> select * from ermap I have a table which contains all the (hundreds of thousands of) current assignments. Here's what I've got so far in an SQL Fiddle: !2/39c51/2/0 I'm writing a query to assign users and their respective domains to IP addresses. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |