Hi, All.

I'm having the following table that holds content structure(nested):
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | | | |
| ns_id | int(11) | NO | PRI | NULL | auto_increment |
| ns_level | int(11) | NO | | 0 | |
| parent_id | int(11) | YES | | NULL | |
| ns_left_index | int(11) | NO | | 0 | |
| ns_right_index | int(11) | NO | | 0 | |
| ns_order | int(11) | NO | | 1 | |
| ns_ignore | int(11) | NO | | 0 | |
| type_id | int(11) | YES | | NULL | |
| name_id | int(11) | YES | | NULL | |
| acl_id | int(11) | NO | | 0 | |
+----------------+---------+------+-----+---------+----------------+

And the actual content of the items from the first table is stored in
different tables, linked by `ns_id` field. The table is chosen by
type_id field.
So, when I need to load some content, I'm doing SELECT ... FROM <first
table>, getting `ns_id` from there and making the second SELECT to
another table.
Is there a way to wrap that in one query?
I was thinking about some JOIN, but I can't provide the second table
name.
Relations between type_id's and tablenames could be stored in some
temporary table.
But would that help?

On Aug 4, 9:33 am, serg.buslov...@gmail.com wrote:
> Hi, All.
>
> I'm having the following table that holds content structure(nested):
> +----------------+---------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------------+---------+------+-----+---------+----------------+
> | id | int(11) | NO | | | |
> | ns_id | int(11) | NO | PRI | NULL | auto_increment |
> | ns_level | int(11) | NO | | 0 | |
> | parent_id | int(11) | YES | | NULL | |
> | ns_left_index | int(11) | NO | | 0 | |
> | ns_right_index | int(11) | NO | | 0 | |
> | ns_order | int(11) | NO | | 1 | |
> | ns_ignore | int(11) | NO | | 0 | |
> | type_id | int(11) | YES | | NULL | |
> | name_id | int(11) | YES | | NULL | |
> | acl_id | int(11) | NO | | 0 | |
> +----------------+---------+------+-----+---------+----------------+
>
> And the actual content of the items from the first table is stored in
> different tables, linked by `ns_id` field. The table is chosen by
> type_id field.
> So, when I need to load some content, I'm doing SELECT ... FROM <first
> table>, getting `ns_id` from there and making the second SELECT to
> another table.
> Is there a way to wrap that in one query?
> I was thinking about some JOIN, but I can't provide the second table
> name.
> Relations between type_id's and tablenames could be stored in some
> temporary table.
> But would that help?

Technically you can't do this -- it indicates a serious problem with
your table design.

Having said that you can kludge it (to a point). Suppose your type_id
was actually a varchar that had the actual table name (tables x, y,
and z) and you wanted to get the value of the "foo" column that lives
in those tables. Then you could do something like this:

select a.ns_id,
case a.type_id
when 'x' then x.foo
when 'y' then y.foo
when 'z' then z.foo
end foo
from a
left outer join x
on a.ns_id = x.ns_id
left outer join y
on a.ns_id = y.ns_id
left outer join z
on a.ns_id = z.ns_id

Obviously this can get pretty ugly and becomes rather inflexible as
you add more tables.

As I said before, though, the right answer is to correct your schema.

On Aug 4, 9:05 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Aug 4, 9:33 am, serg.buslov...@gmail.com wrote:
>
>
>
> > Hi, All.
>
> > I'm having the following table that holds content structure(nested):
> > +----------------+---------+------+-----+---------+----------------+
> > | Field | Type | Null | Key | Default | Extra |
> > +----------------+---------+------+-----+---------+----------------+
> > | id | int(11) | NO | | | |
> > | ns_id | int(11) | NO | PRI | NULL | auto_increment |
> > | ns_level | int(11) | NO | | 0 | |
> > | parent_id | int(11) | YES | | NULL | |
> > | ns_left_index | int(11) | NO | | 0 | |
> > | ns_right_index | int(11) | NO | | 0 | |
> > | ns_order | int(11) | NO | | 1 | |
> > | ns_ignore | int(11) | NO | | 0 | |
> > | type_id | int(11) | YES | | NULL | |
> > | name_id | int(11) | YES | | NULL | |
> > | acl_id | int(11) | NO | | 0 | |
> > +----------------+---------+------+-----+---------+----------------+
>
> > And the actual content of the items from the first table is stored in
> > different tables, linked by `ns_id` field. The table is chosen by
> > type_id field.
> > So, when I need to load some content, I'm doing SELECT ... FROM <first
> > table>, getting `ns_id` from there and making the second SELECT to
> > another table.
> > Is there a way to wrap that in one query?
> > I was thinking about some JOIN, but I can't provide the second table
> > name.
> > Relations between type_id's and tablenames could be stored in some
> > temporary table.
> > But would that help?
>
> Technically you can't do this -- it indicates a serious problem with
> your table design.
>
> Having said that you can kludge it (to a point). Suppose your type_id
> was actually a varchar that had the actual table name (tables x, y,
> and z) and you wanted to get the value of the "foo" column that lives
> in those tables. Then you could do something like this:
>
> select a.ns_id,
> case a.type_id
> when 'x' then x.foo
> when 'y' then y.foo
> when 'z' then z.foo
> end foo
> from a
> left outer join x
> on a.ns_id = x.ns_id
> left outer join y
> on a.ns_id = y.ns_id
> left outer join z
> on a.ns_id = z.ns_id
>
> Obviously this can get pretty ugly and becomes rather inflexible as
> you add more tables.
>
> As I said before, though, the right answer is to correct your schema.

well, that's ok for me. cause queries are generated automaticly.
there's only 1 problem: I need it to be MySQL>=3.23 compliant.

is that structure Ok for 3.23?

On Aug 4, 5:00 pm, serg.buslov...@gmail.com wrote:
> On Aug 4, 9:05 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
>
>
>
> > On Aug 4, 9:33 am, serg.buslov...@gmail.com wrote:
>
> > > Hi, All.
>
> > > I'm having the following table that holds content structure(nested):
> > > +----------------+---------+------+-----+---------+----------------+
> > > | Field | Type | Null | Key | Default | Extra |
> > > +----------------+---------+------+-----+---------+----------------+
> > > | id | int(11) | NO | | | |
> > > | ns_id | int(11) | NO | PRI | NULL | auto_increment |
> > > | ns_level | int(11) | NO | | 0 | |
> > > | parent_id | int(11) | YES | | NULL | |
> > > | ns_left_index | int(11) | NO | | 0 | |
> > > | ns_right_index | int(11) | NO | | 0 | |
> > > | ns_order | int(11) | NO | | 1 | |
> > > | ns_ignore | int(11) | NO | | 0 | |
> > > | type_id | int(11) | YES | | NULL | |
> > > | name_id | int(11) | YES | | NULL | |
> > > | acl_id | int(11) | NO | | 0 | |
> > > +----------------+---------+------+-----+---------+----------------+
>
> > > And the actual content of the items from the first table is stored in
> > > different tables, linked by `ns_id` field. The table is chosen by
> > > type_id field.
> > > So, when I need to load some content, I'm doing SELECT ... FROM <first
> > > table>, getting `ns_id` from there and making the second SELECT to
> > > another table.
> > > Is there a way to wrap that in one query?
> > > I was thinking about some JOIN, but I can't provide the second table
> > > name.
> > > Relations between type_id's and tablenames could be stored in some
> > > temporary table.
> > > But would that help?
>
> > Technically you can't do this -- it indicates a serious problem with
> > your table design.
>
> > Having said that you can kludge it (to a point). Suppose your type_id
> > was actually a varchar that had the actual table name (tables x, y,
> > and z) and you wanted to get the value of the "foo" column that lives
> > in those tables. Then you could do something like this:
>
> > select a.ns_id,
> > case a.type_id
> > when 'x' then x.foo
> > when 'y' then y.foo
> > when 'z' then z.foo
> > end foo
> > from a
> > left outer join x
> > on a.ns_id = x.ns_id
> > left outer join y
> > on a.ns_id = y.ns_id
> > left outer join z
> > on a.ns_id = z.ns_id
>
> > Obviously this can get pretty ugly and becomes rather inflexible as
> > you add more tables.
>
> > As I said before, though, the right answer is to correct your schema.
>
> well, that's ok for me. cause queries are generated automaticly.
> there's only 1 problem: I need it to be MySQL>=3.23 compliant.
>
> is that structure Ok for 3.23?

What does the MySQL 3.23 manual say about the CASE statement?