jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes What will be the query for this logic. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "What will be the query for this logic." Watch "What will be the query for this logic." New topic

What will be the query for this logic.

Bhag Aditya

Joined: Jun 30, 2006
Posts: 4
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

Joined: Jun 06, 2006
Posts: 161
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.
Shaan Shar
Ranch Hand

Joined: Dec 27, 2005
Posts: 1249

Hey Bhag,

Have you tried the code.

Let us know if the code is running sucessfully.

Bhag Aditya

Joined: Jun 30, 2006
Posts: 4
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
subject: What will be the query for this logic.
It's not a secret anymore!