Suppose there is a table called IT_SUP having 2 columns 1. Item_Number 2. Supplier.
An Item can be supplied by mulitple suppliers and a supplier can supply multiple Items.
We have to fetch the Item_Numbers from this table which is supplied by multiple suppliers.???
NB: There are about a million items
Thankx in Advance.
Joined: Jun 06, 2006
The ability to efficiently do this will depend on what features your database supports.
For example, if your database is postgresql, using the group by clause in your query, then to "find all item_numbers for which there are more than one supplier" (assuming that the item_number, supplier combination is unique in this table) might look like:
This would get the item_numbers for which there are more than occurence (supplier) in the table.
though you would probably also want to join this with the items table to display more than just the item id. This example uses the group by clause as a subquery that is joined with (the items) table, which is useful to read all the needed fields in one query.
assuming that there is a table ITEM with at lest columns (ID, NAME).
if there are manyresults and you would like to show them as so many per page, you may be able to use the limit and offset constructs as well, so the query only returns the subsection that is being displayed. Though, unless you also specify an order by constraint, the items returned in a given group by the limit / offset on their own are not guaranteed to be the same for repeat invocations of the same query, as when the data is modified.
Error: Keyboard not attached. Press F1 to continue.
Joined: Dec 27, 2005
Have you tried the code.
Let us know if the code is running sucessfully.
Joined: Jun 30, 2006
The query is returning the item nos, but i need the suppliers too... Let me try explaining the situation again.
Supplier � 1,2,3 provide item 100 to the company.
Now I have to write a query which finds: Only those items that are supplied by different suppliers
So this query should give the result that Item 100 is being provided by supp 1,2,3.
Both these fields are in the same table. Query on that table only. Key is supplier nt item. But the combination is unique