Tag Info

New answers tagged

2

2nd query: Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to. 1st query: An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and ...


0

In the second query you ommited nomencladores.pais.* in the select and as there can be several pais_id for each distribuidor_producto_solicitud.producto_solicitud_id it is very likely that you are getting repeated results for this reason. However, if the only problem is that you are getting repeated results the only thing you need is to add the keyword ...


3

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


1

Exclude users without emails Assuming we only want users that actually have emails. Users without emails are ignored. The reason I went with this assumption at first is that all your queries do that already: LEFT JOIN emails on users.id = emails.user_id WHERE emails.email LIKE 'a' || '%%' By adding a WHERE condition on emails.email you effectively ...


1

You need to get the maximum address_id per user_id SELECT user_id,MAX(address_id) address_id FROM users_addresses GROUP BY user_id; Make that query a subquery and join back to the other tables SELECT u.first_name , u.last_name , u.username , u.password , a.user_id , a.street , a.email , c.name FROM (SELECT user_id,MAX(address_id) address_id FROM ...


1

YOUR ORIGINAL QUERY select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id; PROPER SYNTAX select event_id, count(*) as num_bookmarks from event_connections join events on events.id=event_connections.event_id where event_connections.connectiontype = ...


5

What are the possible reasons of the slow table insert? What are ways to identify this bottleneck without the execution plan? Read How to analyse SQL Server performance, specially the part about Analyzing individual query execution wait times. What actions can I take to reduce the cost of the table insert? That would depend largely on the result ...


2

This would be more efficient: With json and json_array_elements() in pg 9.3 SELECT p.id AS p_id, p.data AS p_data , c.id AS c_id, c.data AS c_data FROM test p LEFT JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE LEFT JOIN test c ON c.id = pc.child::text::int; Use the -> operator instead of ->> in the reference to ...


1

Nevermind, I found the way SELECT * FROM ( SELECT *, json_array_elements((data->>'children')::JSON) child FROM test) x1 LEFT JOIN test x2 ON x1.child::TEXT::INT = x2.id ; id | data | child | id | data ----+--------------------------------------+-------+----+----------------------------------- 1 ...


1

This is your original query SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName LIKE '%castname%' There are three things you can do SUGGESTION #1 You should reorganize the query so that the castName is searched first SELECT DISTINCT ...


1

I think you need a UNION or an OR with EXISTS: SELECT c.* FROM change AS c WHERE c.project_id = 10 OR c.project_id IS NULL AND EXISTS ( SELECT * FROM change AS x WHERE x.project_id = 10 AND x.object_id = c.object_id ) ;


5

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query ...


0

I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL: SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, ...


0

Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause. SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, CheckViewHrsErn.CHECK_DT, SUM(CheckViewHrsErn.EARNINGS) FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn LEFT JOIN PS_JOB JobDta ON ...


1

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly. Let's take your query and look for 'tom' SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info ...


0

I'm no expert on the subject, but I'd recommend you look into FULLTEXT searches and indexes. From what I understand, they're much more efficient than LIKE statements for searching for part of a string. Here's the documentation on the feature.


0

Your inner query should look like SELECT DISTINCT rp.post_id, CASE WHEN pm.meta_key = 'htk_premiere_date' THEN pm.meta_value ELSE '' END AS premiere_date FROM wp_related_people AS rp JOIN wp_posts AS p ON rp.post_id = p.ID JOIN wp_postmeta AS pm ON pm.post_id = p.ID WHERE ...


1

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates: test=# SELECT 1 UNION SELECT 1; ?column? ---------- 1 (1 row) There is a sharp distinction between UNION and UNION ALL: test=# SELECT 1 UNION ALL SELECT 1; ?column? ---------- 1 1 (2 rows) in your case the second subselect can never ...


1

-- ---------------------------- -- Table structure for adresses -- ---------------------------- DROP TABLE IF EXISTS `adresses`; CREATE TABLE `adresses` ( `adress_id` int(11) NOT NULL AUTO_INCREMENT, `adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...


0

Best solution. Thanks to @Marshall Matters SELECT c.*, a1.adress_adress AS company_adress_adress, a1.adress_adressnumber as company_adress_adressnumber, a1.adress_zipcode as company_adress_adress_zipcode, a1.adress_zipcode as company_adress_adress_city, a2.adress_adress AS postal_adress_adress, a2.adress_adressnumber as postal_adress_adressnumber, ...



Top 50 recent answers are included