New answers tagged

0

The hierarchy_table has 5 columns that all reference the name_table, so you need 5 joins. It may be better to use LEFT joins instead of INNER, in case some of these columns are nullable and you still want the rows returned: SELECT o.name AS object, p1.name AS parent_1, p2.name AS parent_2, c1.name AS child_1, c2.name AS child_2 ...


1

You can use alias name for tables involved in query. select b.name object, c.name parent_1, d.name parent_2 from hierarchy_table a, name_table b, name_table c, name_table d where a.object_id = b.name_id and a.parent_id_1 = c.name_id and a.parent_id_2 = d.name_id


0

This would be better accomplished through a series of inserts. Create a temporary table that contains all of the columns from table Appointments and table Events. Insert into the temp table using each of your join predicates in the order of importance that you have listed above. For each insert after the first one (the one joined on appointment_id), use the ...


0

The first query will be faster this way: select * from `entries` where EXISTS ( select * from `users` where `entries`.`profile_id` = `users`.`id` ) order by `id` desc It needs an index (PRIMARY KEY?) on users.id. It is faster because the subquery quits when finds one row, rather than counting all of them. The query part ...


5

Both queries are exactly the same, should return exactly the same result set and should produce exactly the same Execution plan. BUT the second query is using ANSI-92 SQL syntax . The first one is using the older (very old) version of SQL Joins. I can think of a few reasons why you should use the ANSI-92 SQL Syntax. It separates the Join Conditions ...


1

FROM ( SELECT ... ) JOIN ( SELECT ... ) does not optimize well. Think of a better way to write the query. If that fails, put one of the subqueries into a TEMPORARY TABLE and add an index to it. Consider using the datatype DATE, not INT, for dates. OR is a performance killer (because it prevents use of an index). Consider other ways to deal with IS ...


1

First, add these indexes: ALTER TABLE cat_01 ADD INDEX `ShopBuyDates` (`shop_id`,`buy_date`) ALTER TABLE orders ADD INDEX `OrderSales` (`order_id`,`sales`) Then try this query, and report the results. Make sure to run it at least twice, and discard the first result's performance, to flush out the effects of populating the cache. SELECT 'cat_01' as ...


1

JOIN is not that effective, because you need to access the table 4 times. With PIVOT you can do it with 1 table access. create table list_of_stuff ( list_id number(5,0), item varchar2(40 char) ); insert all into list_of_stuff values (47, 'Baseball') into list_of_stuff values (47, 'Hat') into list_of_stuff values (47, 'Gloves') into ...


1

I think this will give you desired resultset : select id,grp,itdesc, MAX(CASE WHEN su=1 THEN DEN END) AS DEN, MAX(CASE WHEN su=1 THEN NUM END) AS NUM, MAX(CASE WHEN su=1 THEN SU END) AS SU , -- or just "1 AS SU" MAX(CASE WHEN cs=1 THEN NUM END) AS CS, MAX(CASE WHEN sw=1 THEN NUM WHEN sw=0 THEN 0 END) AS SW FROM tab GROUP BY id,grp,itdesc


0

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle: select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds" from container 1. SELECT ...


1

This looks like a good fit for Postgres' JSON functions: select to_json(x) from ( select c.*, json_agg(ct.thing_id) as "thingIds" from container_thing ct join container c on ct.container_id = c.id group by c.id ) x SQLFiddle example: http://sqlfiddle.com/#!15/cd9992/1


1

Join + string_agg() select '{"id": "' || cast (c.id as varchar(36)) ||', "thingIds": ["' || string_agg(cast(ct.thing_id as varchar(36)),'","') || '"]}' from container c join container_thing ct on c.id=ct.container_id group by c.id;


1

A join (on container_id) will work very well in this situation. Yes, you will get multiple rows with duplicated container data. If your coding language supports an ORM (e.g. MyBatis or Hibernate for Java), the redundant duplication of container fields will be handled for you. The ORM will return a list of containers with each container having a list of IDs. ...


3

In Postgres the most efficient way is to do this using distinct on (): select distinct on (o.id) o.id, o.name, t.edu, t.sortby from one o join two t on o.grade = t.grade_id order by o.id, t.sortby desc


0

Yes, problem one is that you are mixing explicit (SQL-92) JOIN syntax with implicit join, , (SQL-89) comma syntax. While this is allowed and with some care it may even work correctly, it's very easy to get this type of errors. The FROM part of your query: from courses as co , course_type as ct left join course_leaders as cl on cl.course_leaders_id = ...


0

Normalise the table responses so there is 1 question per row. Then you are also not limited to a number of questions per survey.


1

For the simple result (just the id), EXCEPT might be simplest and fastest: Also, since you write: I get their primary keys in an array Simply use unnest() and don't waste time joining to the contacts table: SELECT id FROM unnest('{1,10,20,1557,5000,15057}'::int[]) id -- actual array (not list) EXCEPT ALL SELECT contact_id FROM case_contacts EXCEPT ...


2

You may need to alter this slightly for postgres (is is MS SQL syntax) but something like: SELECT id FROM contacts LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id LEFT OUTER JOIN FROM invoices ON invoices.contact_id = contacts.id WHERE ...


0

Update Syntax for PostgreSQL is different: [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_list ] [ ...


11

I suspect the difference is the implicit conversion, which can get in the way. As an example, Bar isn't touched if it has a compatible data type: CREATE TABLE [dbo].[Bar] ( [Value] int NULL, [Value2] int NULL );


4

The WHERE date(attIn) like '2016-07-02%' is converting the LEFT join to an INNER join. The condition should be moved to the ON clause. Also: It's not good practise to use LIKE for dates comparison Using functions on columns (like the date()) before comparing it makes indexes useless. It's better to make the condition sargable. The query corrected: ...


1

Your LEFT JOIN delivers the missing attendanceIn fields as NULL. If you still want this result then you must add: or attIn is NULL at the end of your query. Now you will get the expected result.


2

To follow on from my comment. I'd recommend a date table. Sample Customer Purchase Data IF OBJECT_ID('tempdb..#CustomerPurchases') IS NOT NULL DROP TABLE #CustomerPurchases GO CREATE TABLE #CustomerPurchases (PurchaseDate date, CustomerName varchar(20)) INSERT INTO #CustomerPurchases (PurchaseDate, CustomerName) VALUES ('2016-07-04','Jon Snow') ,('2016-...


0

That should also work more efficient than Willem solution SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout FROM employees LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno LEFT JOIN attendanceOut ON employees.eno=attendanceOut.eno WHERE DATE(attendanceIn.puchtime) = '2016-07-01' AND DATE(...


1

Do not use functions on your columns you are comparing against, as that prevents the query from using any indexes you may have. Start with this: SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout FROM employees LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno LEFT JOIN attendanceOut ON ...


1

I would use a subquery: ... FROM historialalumno h FULL JOIN ( SELECT * FROM inasistencias WHERE fecha = '2016-06-07' ) i ON i.idhistorialalumnofk = h.id ... And don't use ambiguous date formats. Use unambiguous ISO format.


2

You should be able to get that by simply adding the additional criteria to the full join instead of the where clause. on i.idhistorialalumnofk = h.id and i.fecha = '06/07/2016' it seems odd to be needing a FULL join here but we don't really have much info so that might actually be the case.


1

"last_active_user" sounds like ( SELECT user_login FROM users WHERE ... ORDER BY ... DESC LIMIT 1 ) count(comments.comment) sounds like ( SELECT COUNT(*) FROM abc_comments WHERE ... ) That is, get rid of the joins and replace the aggregate values by subqueries as above. JOIN explodes the number of rows, then You need a GROUP BY to get it back in ...


0

See groupwise max; it includes "top-N".


3

I see some issues: category_pointers is missing a unique key on its natural primary key. Try using (category, isbn) as the primary key. It may be helpful to have the reverse index ('isbn', 'category'). This query can run strictly on the index. category_pointers has a surrogate key for its primary index. I would drop it and use the natural key (category,...


2

Logic is very simple. We are taking two instance of same table; second in a subquery. We pick first salary of main table and compare it against all salaries in subquery table to get a count of salaries greater than the salary in main table under consideration. If count is N-1; then it implies that salary in main table is Nth max salary because there are N-1 ...


1

It sounds like you're talking about a self-referential relationship, especially with the inclusion of "manager" titles. This is a common pattern, especially with employee tables. While they may implement it differently the main RDBMS vendors typically offer recursive capabilities. This means that with a special syntax you can easily query this kind of a ...


-3

select * from (select rownum as rn,salary from employees order by salary desc )x where x.rn = 5 -- nth highest salary



Top 50 recent answers are included