Re: Re: [LIP] An SQL question about using multiple tables

On Thursday 09 Sep 2004 11:07 am, vivek khurana wrote:
> --- Sandip Bhattacharya <[email protected]> wrote:
>
>  Can you specify the problem in full, it will be
> helpful in writing down the query.  As 3 tables dont
> have primary key you might have to resolve to other
> techniques.

Giving an example for the B table, which I call an attribute table. A can have 
one or more attributes. B stores the language attribute.

Right now A has
(orgid,name,...
--------------------
1,ASSOCHAM
2,PHD
3,NASSCOM

B has
(orgid,langid
--------------------
1,1
1,2
2,1
2,2
2,3
.... and so on

MASTER_LANG a master table has
(langid, langname)
------------------------------
1, english
2, hindi
3,gujarati

(I could have eliminated MASTER_LANG and put all data directly into B .. but 
MASTER_LANG is referenced in some other attribute tables too ..for other 
"object" types)

Similarly C and D have other attributes for the object represented by A.
 
I am trying to get a search function in place which, given varied combinations 
of the attributes, returns me a list of matching objects.

E in my problem is a poor attempt at table inheritance which mysql  doesnt 
support. E stores a set of attributes which are common to some other object 
types. Thankfully(?), unlike other attribute tables, E has a one-to-one 
correspondence to the rows of A. Think of E as an extension of an A row.
So to get the full value of an A row, I have to left join an A row to its E 
counterpart.

The requirement can be also understood using a select using sub-queries which 
could have solved my problem. I am repeating it again below: Here the search 
function has been called with the B attribute as 'XXX', C attribute as 'YYY' 
and D as 'ZZZ'.

The SQL with subqueries will probably be something like:
============================================
select A.*, E.* from A inner join E on A.id=E.parentid 
   where 
      A.id in (select distinct A.id from A inner join B on A.id=B.parentid 
                 where B.name='XXX')
  and 
      A.id in (select distinct A.id from A inner join C on A.id=C.parentid 
                 where C.name='YYY')
  and 
      A.id in (select distinct A.id from A inner join D on A.id=D.parentid 
                 where D.name='ZZZ');
===============================================

- Sandip

-- 
Sandip Bhattacharya    *    Puroga Technologies   *     sandip-/[email protected]
Work: http://www.puroga.com        *         Home: http://www.sandipb.net

PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3

Two peanuts were walking through the New York.  One was assaulted.