There are applications, the application has parameters, each parameter in its table. You must select the applications that are suitable under the conditions. (those for where everything is known) Everything seems to be simple, but the eye is already zamylilsya, I am dull somewhere.

In different applications can be specified different number of parameters.

An example from the life of this - to pick up an object from the heading "I will rent", announced from the heading "I will rent" (those who want to remove)

scheme

alt text

let's say:

application # 1
prop1 = 25 and 6
prop2 = 5

application # 2
prop1 = 25

under the initial condition:
prop1 = 25 you need to select 2 applications
prop2 = 5 choose 1st order
prop2 = 7 choose none
prop3 = 6 choose none

  • so what's the question then? - Max Zhukov
  • ummm .. are you suggesting a request be written for you? It is unknown to which table and from there only you know what data? - DreamChild 4:08 pm
  • Well, I do not expect a request, I need to understand the general direction. just now I am writing a bunch of joins and a footwoman from the conditions, but it turns out a request for the task "select objects that can be removed", those are the inverse problem ( - pen-exe
  • Well, then at least you should outline the subject area - what tables you have, what fields they have, and what you want to get. Otherwise, it turns out fortune telling on the coffee grounds - DreamChild
  • @ pen-exe is better instead of any schemes make an example on sqlfiddle - zb '11

3 answers 3

As far as I understand, this is an implementation variant of EAV . Not very successful, let's face it, but oh well.

You need to make a request for applications with various parameters, something like:

select r.id, r.title p1.value as p1value, p2.value as p2value, ... pn.value as pnvalue from request r left join prop1 p1 on r.id = p1.req_id left join prop2 p2 on r.id = p2.req_id ... left join propn pn on r.id = pn.req_id

Such a request would be the easiest way to form a cycle, but I do not know what real table names you have there and whether the fields are different, in any case you need to be able to assemble such a request. And if the number of parameter tables you have is fixed, then you can hard-code it in the end (at the same time, of course, the meaning of EAV is lost, but okay, too) ...

Then just add a filter to this query:

 where p1.value in (1,3,7) and p2.value > 10 ... and pn.value between 30 and 40

Well, it is clear that filters can be any - according to one or several parameters. The point is that if the application does not have a parameter for which the filter goes, then it will be dropped, and if it is, it will be filtered by value.

In order to select the applications "I will take" for the application "I will rent" and vice versa, simply take the values ​​for the filter according to the necessary parameters and substitute the conditions in the request above.

  • everything would be fine, but in this case, the application will appear in which all the parameters are filled. And it is necessary that even if 1 is the only one and he sovapl, then withdraw. - pen-exe
  • He finally tasted) or is null for each condition - pen-exe

You really do not judge me, I admit, in your scheme with all these prop1, prop2 and prop3 I understood little. Nevertheless, based on the previously mentioned condition about “pass” - “take off” I would do something like this:

  1. Would make a table of users (those who post ads on the surrender or shooting)
  2. Would make a table of types of objects to be handed over / removed (one-room apartment, one-bedroom apartment, three-room hut, etc.)
  3. I would add a stable link for connecting the previous two tables. In it, in addition to the primary key, would create two external to the two above tables, as well as the fields "Type of offer" (rent or withdraw) and "Price"

Further, in order to “pick up an object from the category“ I will rent ”, we will declare it from the category“ I will take it off ”,” you need to make a simple join indicating the type of real estate of interest and, possibly, the price fork. For greater normalization, you can spread on separate tables the types of real estate (apartment, room, cottage) and its attributes (one-room, two-room, etc.), put in a separate table the type of offer (I will rent, rent, buy, sell)

I do not know how my model fits your subject area, but I still hope that I helped with something

    use subqueries.

     SELECT ... FROM table1 WHERE ... IN (SELECT ... FROM ...);
    • and what is better than joins? - DreamChild