File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What will be the query for this logic.

 
Bhag Aditya
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

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.
 
Travis Hein
Ranch Hand
Posts: 161
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Shaan Shar
Ranch Hand
Posts: 1249
Java Spring Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Bhag,

Have you tried the code.

Let us know if the code is running sucessfully.


 
Bhag Aditya
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

Thanks for the interest!!
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic