Reverse lookup

[LAMDB]

Hi there,

I have a master detail set of tables with a one-to-many relationship. Let's call the tables Projects (master) and Markets (details).

I need to allow users to query the database and be able to select projects by markets. In other words, users should be able to "say": "List all projects where Market is Architecture and Construction"

The query is to be created dynamically (this is not the problem here) and there are lots of projects and lots of markets.

Can anyone guide me on how to create the query (conceptually).

Below is a simplified version of the two tables. Now, I would like to query: "list all projects that is offices and hospitality"; with this sample data I should get project 001 only

Projects:
----------
PrjId PrjName... etc
001 myProject
...

Markets:
---------
PrjId MrktId Mrkt1 Mrkt2 Mrkt3 ... etc
001 001 offices single-family NULL
001 002 hospitality NULL NULL
001 003 retail NULL Something
002 004 offices NULL NULL
003 005 hospitality NULL NULL

[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

[tesuji]

Hi again

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

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

[LAMDB]

Hi Tesuji,

As you imagine correctly, I have no power to re-design the Markets Table. I am only trying to query the database of a system that is being used in the company that am doing the work for. It is never that easy is it?

I have to find a solution to work with the setup that I described above. Sorry I did not mentioned this before.

Thanks for your reply.

LAM.

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

[LAMDB]

Let me add something else that might have been misinterpreted.

Columns Mrkt1, Mrkt2, MrktN do not contain the same information. To make this clearer, let me share some real data. The following data would be lookup data for each column in the Markets table.

Aviation (Mrket1)
Terminals
Lounges
...

Maritime (Mrket2)
Ports
Cruise Terminals
...

Education (Mrket3)
Pre-k
High-Schools
...

Also, to answer your question, They have only 8 markets at the moment, and it is not expected to grow considerably. Maybe a couple more.

Thanks again.

LAM.

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

[tesuji]

hi,

you should consider my posting #3:

You can freely change the IN list

WHERE Mrkt2 IN ('architecture', 'offices', 'hospitality')

for example

WHERE Maritime IN ('Cruise Terminals', 'ports')

So you need 8 constructions like:
UNION
SELECT PrjID FROM markets WHERE Mrktxx IN (List of items)

This can easily be generated at runtime.

tesu

[LAMDB]

Hi there,

I did the following test and it did not returned the expected results. Let me share it.

The statement:
select distinct prjId, Mrkt1 from markets
where Mrkt1 in ('Retail', 'Offices' )

The Idea:
To get all projects that are both, Retail and Offices

The data:
Mrkt Table
prjid Mrkt1
0100 Hospitality
0100 Retail
0200 Retail
0200 Offices

The EXPECTED result:
prjid = 0200

The ACTUAL result:
prjId = 0100, 0200

As far as I am concerned, "where Mrkt1 in ('Retail', 'Offices' )" is working as an OR

LAM.

Hi again

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

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

[tesuji]

Hello

That is true, because the IN list is kind of set, all records will be fetched that contain one of the list's element. So you can say the records are "ored" together in result set.

I assume that you can't determine whether a project, for example Terminals, entered by a user would be found in market Aviation only. If not, one has consider almost all permutations to be made of a set, for example given user entries (offices, single-family, retail, hospitality) would then dwindle into (offices, single-family, retail, hospitality), (single-family, offices, retail, hospitality), (retail, single-family,offices , hospitality) ...

So what to do? To get to create effective sql selects, I see two ways to proceed: 1. Re-structuring Markets table temporarily by means of temporary views, stored procedures (transact SQL programs), or applying new WITH clause.

2. Doing some c++, java, or c# programming using ado, odbc, jdbc db-interfaces.

Maybe the latter way is also the more appropriate one, if the some-day selected data are further processed by c++/java etc programs written by yourself. Can you tell me what you want to do with that data?

I will give some thoughts to these problems.

-----
tesu