Tag Info

Hot answers tagged

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 | ...


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, ...


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 ...


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 ...


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 ...


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 ...


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 ...


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

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 ...


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 = ...


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 = ...



Only top voted, non community-wiki answers of a minimum length are eligible