how to create a dynamic table

[wizardry]

Hello -

I'm trying to create a dynamic results table layout from 4 tables.

I want to have the data from the 2 tables asstatusupdate and asstatusdata even if the other two tables do not have a matching record.

I also want to group the tables results for asstatusupdate and asstatusdata i've tried this in sql but did not get the results that i need.

i need to have it retrive one record for asstatusupdate and asstatusdata and many records for asmanystatusupdate and ascomments. grouping by asstatusupdate.

Thanks in advance for your help!

here is the query:

SELECT a.Id, a.Type, 
a.Dates, 
a.UIdFk, 
b.Id as Did, 
b.comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as Memo
FROM 
asstatusupdate as a,  
left join asstatusdata as b 
on a.Id=b.sfk 
left join asmanystatusupdate as c 
on b.sfk=c.sfk
left join ascomments as d 
on c.cfk=d.id
where a.uidfk='1'
 

[dlite922]

"create dynamic table" ? 4 tables?

You gave 2, where's the other two.

I think you're going about your logic wrong.

Tell us your data and what you need from it, i.e. explain the query with plain English language (The business requirement).

Dan

[wizardry]

i need to get the status, and group the comments for each status.

here are the four tables:
asstatusupdate is the catagory
asstatusdata is the data
asmanystatusupdate is the many table
ascomments is the comments table

for example:
status 0
comment 1
comment 2
status 1
comment 3
comment 4

mysql> desc asstatusupdate;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Id    | bigint(20) unsigned | NO   |     | 0       |       |
| Type  | longblob            | NO   |     | NULL    |       |
| Dates | datetime            | NO   |     | NULL    |       |
| UIdFk | bigint(20) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.08 sec)
 
mysql> desc asstatusdata;
+---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| Id      | bigint(20)          | NO   |     | 0       |       |
| Comment | longblob            | NO   |     | NULL    |       |
| Dates   | datetime            | NO   |     | NULL    |       |
| SFk     | bigint(20) unsigned | NO   |     | NULL    |       |
+---------+---------------------+------+-----+---------+-------+
4 rows in set (0.09 sec)
 
mysql> desc asmanystatusupdate;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Id     | bigint(20) unsigned | NO   |     | 0       |       |
| SFk    | bigint(20) unsigned | NO   |     | NULL    |       |
| CFk    | bigint(20) unsigned | NO   |     | NULL    |       |
| CFk0   | bigint(20) unsigned | NO   |     | NULL    |       |
| UIdFk0 | bigint(20) unsigned | NO   |     | NULL    |       |
| UIdFk1 | bigint(20) unsigned | NO   |     | NULL    |       |
| Dates  | datetime            | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
7 rows in set (0.07 sec)
 
mysql> desc ascomments;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Id    | bigint(20) unsigned | NO   |     | 0       |       |
| Memo  | longblob            | NO   |     | NULL    |       |
| Date  | datetime            | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.11 sec)
 
mysql> 
 

[Rabbit]

Can you provide some sample data and results you expect from said data?

[wizardry]

here is the results from the query that is posted.

my ultimate goal is to have many types/comments with many memos.

here is the link for the results report:
http://www.only1wizard.com/billboardsquery.pdf

[Rabbit]

I was looking more for a sampling of the source data and the results you would he looking for from the query.