Databases
StackOverflow2013
Postsdbo
POsql 2000 select id where multiple row conditions are met
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsql 2000 select id where multiple row conditions are met
    primarykey
    data
    text
    <p>I am struggling to get my head around this sql.</p> <p>I have a function that returns a list of items associated with a Bill of Materials BOM.</p> <p>The result of the sql select </p> <pre><code>SELECT BOM, ITEMID, QTY FROM boms WHERE bom='A' </code></pre> <p>is </p> <pre><code>BOM | ITEMID | QTY A | ITEMB | 1 A | ITEMC | 2 </code></pre> <p>Now using that result set I am looking to query my salestable to find sales where ITEMB and ITEMC were sold in enough quantity.</p> <p>The format of the salestable is as follows</p> <pre><code>SELECT salesid, itemid, sum(qtyordered) 'ordered' FROM salesline WHERE itemid='ITEMB' or itemid='ITEMC' GROUP BY salesid, itemid </code></pre> <p>This would give me something like</p> <pre><code>salesid | itemid | ordered SO-10000 | ITEMB | 1 SO-10001 | ITEMB | 1 SO-10001 | ITEMC | 1 SO-10002 | ITEMB | 1 SO-10002 | ITEMC | 2 </code></pre> <p>ideally I would like to return only SO-10002 as this is the only sale where all necessary units were sold.</p> <p>Any suggestions would be appreciated. Ideally one query would be ideal but I am not sure if that is possible. Performance is not a must as this would be run once a week in the early hours of the morning.</p> <p>EDIT</p> <p>with the always excellent help, the code is now complete. I have wrapped it all up into a UDF which simply returns the sales for a specified BOM over a specified period of time.</p> <p>Function is </p> <pre><code>CREATE FUNCTION [dbo].[BOMSALES] (@bom varchar(20),@startdate datetime, @enddate datetime) RETURNS TABLE AS RETURN( select count(q.SALESID) SOLD FROM (SELECT s.SALESID FROM ( SELECT s.SALESID, ITEMID, SUM(qtyordered) AS SOLD FROM salesline s inner join SALESTABLE st on st.salesid=s.SALESID where st.createddate&gt;=@startdate and st.CREATEDDATE&lt;=@enddate and st.salestype=3 GROUP BY s.SALESID, ITEMID ) AS s JOIN dbo.BOM1 AS b ON b.ITEMID = s.ITEMID AND b.QTY &lt;= s.SOLD where b.BOM=@bom GROUP BY s.SALESID HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.BOM1 WHERE BOM = @bom)) q ) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USl0ckm4
    1. USl0ckm4
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI'm not getting why you need the first query. Perhaps you could explain the problem a little more clearly? How are you getting the 'sold in enough quantity' amount? Additionally, does your second query even work? I thought to have an aggregate function such as SUM work you had to have a GROUP BY clause?
      singulars
      1. POsql 2000 select id where multiple row conditions are met
      1. USRyan
    2. COHi @Ryan - yes you are right with regards to the second query - this was a very simplified version of the actual select. I have edited it to include the group by. As to why the first - I need to find what components the BOM A is made up of first and how many of each item is required.
      singulars
      1. POsql 2000 select id where multiple row conditions are met
      1. USl0ckm4
    3. COWill this be run only for a single "bom"?
      singulars
      1. POsql 2000 select id where multiple row conditions are met
      1. USdnoeth
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload