Originally Posted by tesuji

hi

If you were able to redesign table Markets like that:

(PrjID, MarkedID, Market), where at least all three attributes be part of markets' primary key, the query you are looking for and generating where clause at runtime would really be simple:

select distinct p.PrjID, PrjName from projects p inner join Markets m
where Market IN ('architecture', 'offices', 'hospitality', ... )

(This select requires that all primary and foreign keys are properly defined. If not, you must extend join Markets m by ON p.PrjID = m.PrjID)

With new table Markets

PrjID MrktId Market
001 001 offices
001 001 single-family
001 002 hospitality
001 003 retail
001 003 Something
002 004 offices
003 005 hospitality

and Projects the result set would be

PrjID PrjName
001 myProject


Unfortunately, current table Market is rather poorly designed. Because of the repeating group Mrkt1, Mrkt2 etc it even violates first normal form. I am afraid, it isn't in your power to simply redesign Markets.

There is a further advantage of new table Markets: no matter how many markets you want to insert you never need to change the structure (schema) of that table.

Btw, how many Mrkt columns has your current table Markets?

krs,
tesu

Originally Posted by tesuji

hi

If you were able to redesign table Markets like that:

(PrjID, MarkedID, Market), where at least all three attributes be part of markets' primary key, the query you are looking for and generating where clause at runtime would really be simple:

select distinct p.PrjID, PrjName from projects p inner join Markets m
where Market IN ('architecture', 'offices', 'hospitality', ... )

(This select requires that all primary and foreign keys are properly defined. If not, you must extend join Markets m by ON p.PrjID = m.PrjID)

With new table Markets

PrjID MrktId Market
001 001 offices
001 001 single-family
001 002 hospitality
001 003 retail
001 003 Something
002 004 offices
003 005 hospitality

and Projects the result set would be

PrjID PrjName
001 myProject


Unfortunately, current table Market is rather poorly designed. Because of the repeating group Mrkt1, Mrkt2 etc it even violates first normal form. I am afraid, it isn't in your power to simply redesign Markets.

There is a further advantage of new table Markets: no matter how many markets you want to insert you never need to change the structure (schema) of that table.

Btw, how many Mrkt columns has your current table Markets?

krs,
tesu

Originally Posted by tesuji

Hi again

I have got this idea of selecting the appropriate data from table Markets:

Help with Code Tags

sql Syntax (Toggle Plain Text)

SELECT DISTINCT PrjID, PrjName FROM projects 
 WHERE PrjID IN
   (
     SELECT PrjID FROM markets WHERE Mrkt1 IN ('architecture', 'offices', 'hospitality')
     union 
     SELECT PrjID FROM markets WHERE Mrkt2 IN ('architecture', 'offices', 'hospitality')
     union
     SELECT PrjID FROM markets WHERE Mrkt3 IN ('architecture', 'offices', 'hospitality')
)

select distinct PrjID, PrjName from projects where PrjID IN ( select PrjID from markets where Mrkt1 IN ('architecture', 'offices', 'hospitality') union select PrjID from markets where Mrkt2 IN ('architecture', 'offices', 'hospitality') union select PrjID from markets where Mrkt3 IN ('architecture', 'offices', 'hospitality') )

I haven't test it so far. But I believe it must be this sort of select statement which can also be dynamically generated during runtime.

Try it an tell me your result.

krs,
tesu