Tag Info

New answers tagged

1

Use a subquery that has the checklist id that has both tags SELECT B.id checklist_id, B.name checklist_name, D.name tag_name FROM ( SELECT AA.checklistId,COUNT(1) tagcount FROM checklist_tag AA INNER JOIN tag BB ON AA.tagId = BB.id WHERE BB.name IN ('tag 1', 'tag 2') GROUP BY AA.checklistId HAVING COUNT(1)=2 ) A INNER JOIN ...


3

This is not a great design. You have a couple of options to improve it: Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints. Put the parent key in the child table. If every child record refers to one master record, ...


1

Assuming you have a recent enough version, infomix supports the LAG and LEAD functions over ranking operations, see http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm These will only work if the current row and the one you consider the previous row are find using the same filtering clauses though, otherwise you and ...


1

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists: SELECT P.product_id AS product_id, ,P.deal_dt AS deal_dt ,C.deal_dt AS previous_deal_dt ,C.cancel_dt AS previous_cancel_dt FROM product_shipping p LEFT JOIN product_shipping C ON C.product_id = ...


0

I'm not sure how to join to get latest previous record. Start there. Get the latest previous record with a self-join: select c.product_id, min(p.deal_dt) as prev_dt from product_shipping as c join product_shipping as p on c.product_id = p.product_id and c.deal_dt >= p.deal_dt group by c.product_id That will produce rows for which prev_dt = ...


0

"ORA-00918: column ambiguously defined" means that both tables contain one or more of the columns in your select and/or predicate, and Oracle doesn't know from which table to pull/query the column. Try putting an alias in front of each table, then putting the same alias in front of each column name. (sorry I couldn't be more specific, without a describe ...


1

select b.*, s.service_name from branches b, branch_services bs, services s where b.branch_id=bs.branch_id and bs.service_id=s.service_id


1

I've made a couple of assumptions here but one of these should work or be close: select inventory.Sku,inventory.SKU_DESCRIPTION,WAREHOUSEID from inventory join warehouse using (warehoueid) where manager = 'lucille smith' Or Select inventory.sku, inventory.sku_description, inventory.warehouseid from inventory, warehouse where inventory.warehouseid = ...


2

You are better off dropping and recreating. Why ? ANALYSIS Let's take the hypothetical example from your question CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ); This is literally a pure Cartesian Product. Let's say tableA has 2,000 rows and tableB has 5,000. A Cartesian product would result in result_table having1,000,000 rows. Now ...


0

I believe you want to show all categories. If so, you will need to make all of your joins RIGHT OUTER JOINS. Why? Associativity and precedence for outer join operators is poorly defined and unpredictable You need to keep going 'outer' once you've started. Because you're using a RIGHT join, each preceding join must also be a RIGHT join. If not, the NULLs ...


0

You have multiple values in the DATE_ADDED field for each distinct pair of ORDERS_ID/VALUE results you see returned when you execute the query with only two columns selected.


3

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation: FULL OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row ...


0

Albert - could you in future please format your code something like this? It makes things much easier for those of us who are trying to help you :-). Use the {} code-formatting widget at the top of the question entry panel. You can also quote text (double-apostrophe ") and put in a link (the chain symbol) and do lists and B Bold &c... CREATE TABLE IF ...


0

You have to join the user table to the rank table twice SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor FROM user u INNER JOIN rank r1 ON u.rank_id = r1.id INNER JOIN rank r2 ON u.supervisor_id = r2.id WHERE u.id = 4; or SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor FROM user u INNER JOIN rank r1 ON u.rank_id = r1.id INNER ...


2

Try this query below. Note it only lists the bug and its derived creation date (i.e.: doesn't include the bugnotes, etc). An OUTER join was preferred to return all bugs, regardless of whether or not they have any associated bugnotes. SELECT bugid , bugtitle , MIN(notetime) created FROM bugs b LEFT OUTER JOIN bugnotes USING (bugid) GROUP BY ...


0

The phrase "but not use the JOIN ON syntax" may mean you should use the older 8i style of joins that doesn't use words "JOIN ON" at all, or it may mean you should use "JOIN USING" rather than "JOIN ON". Hopefully they meant the latter.


0

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors. Something like this will work: SELECT ...


2

I think that you want something like this (see below for table defs). Something appears to have happened my SQLFiddle demo. SELECT bs.bugid, bs.bugtitle, bn.bugnotesid, bn.notetime, bn.notetext FROM bugs bs, bugnotes bn JOIN ( SELECT bugnotesid, MIN(notetime) FROM bugnotes GROUP By (bugid) ) mytab ON bn.bugnotesid = mytab.bugnotesid WHERE ...


3

You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table: select distinct p.product_number, d.date from yourtable p cross join yourtable d; See SQL Fiddle with Demo. This will create a list of data similar to: | PRODUCT_NUMBER | DATE | ...


0

PROPOSED QUERY SELECT B.* FROM (SELECT MAX(changes1.ID) ID,changes1.issue_key FROM `issue_changes` AS changes1 INNER JOIN `issues` AS issue1 ON issue1.`ID` = changes1.`issue_key` WHERE issue1.`project_key` = "bug_tracker" AND changes1.`change_field` = "Status" AND changes1.`change_time` < "1400110321" GROUP BY changes1.issue_key) A LEFT JOIN ...


0

Join conference to person_conference, selecting the person key from person_conference. Do the same for publication and person_publication. Make the two queries sub queries in a from clause and join the two on the person key. Edit: You would want to do something like this: SELECT p.person_id, p.name, a.conference, b.publication FROM ...


0

Instead of doing any LEFT JOIN, try this query PROPOSED QUERY SELECT IFNULL(name,'Total') name, SUM(IF(type='dog',quant,0)) dogs, SUM(IF(type='cat',quant,0)) cats FROM ( SELECT AA.name,BB.type,SUM(BB.quant) quant FROM customers AA INNER JOIN purchases BB ON AA.id = BB.owner_id GROUP BY AA.name,BB.type ) A GROUP BY name WITH ROLLUP; ...


0

Perhaps it is easiest to see why this is happening by removing the aggregate functions: SELECT c.name , dogs.quant , cats.quant FROM customers AS c LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog' LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat' +------+-------+-------+ ...


1

Here are my notices: Make sure that creator exists only in table1, or add [... WHERE table1.creator=..] It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key. From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned ...



Top 50 recent answers are included