SQL Convert One to Many to flat file

[Dave R.]

I am working with a database right now where I only have read-only ODBC
access, and I am using Access to send my queries. I have a main table that
has a one to many relationship with another table. That second table then
has a many to one relationship with a third table. What I want to do is take
the first one table, create another table with enough columns in the other
one file, and then left join the first table to the many table. ie:

Table1:
PID|Name|Address|City|Country

11|Dave|1237 This St|Anywhere|US
12|Chris|1236 This St|Anywhere|US
13|John|1235 This St|Anywhere|US

Table2:
PID|FID

11|1
12|1
13|1
11|2
13|2
12|3
11|4
12|4

Table3:
FID|FoodShort|FoodDesc

1|Cheese|Cheddar, tasty, melts well
2|Milk|Calcium rich, 250ml is one serving
3|Beer|A hearty, pale Ale
4|Pizza|Helps increase waist size

Now I want it to look like this:

View:
PID|Name|Address|City|Country|Cheese|Milk|Beer|Pizza

11|Dave|1237 This St|Anywhere|US|Cheese|Milk|Null|Pizza
12|Chris|1236 This St|Anywhere|US|Cheese|Null|Beer|Pizza
13|John|1235 This St|Anywhere|US|Cheese||Milk|Null|Null

I build that table using this query:

SELECT q01.*,q02.FoodShort as Cheese,q03.FoodShort as Milk,q04.FoodShort as
Beer,q05.FoodShort as Pizza
FROM ((((select P.* from Table1 as P) as q01
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=1]. AS q02
ON q02.pid = q01.PID)
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=2]. AS q03
ON q03.pid=q01.pid)
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=3]. AS q04
ON q04.pid=q01.pid)
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=4]. AS q05
ON q05.pid=q01.pid;

The only issue is that if table3 ever changes then the gig is up. If cheese
get's deleted, and then added again in with an FID of 5 (the good thing is
that referential integrity is maintained in this database, and all the FID
of 4 would disappear from table2) then my query "breaks" as all cheese would
show as null for this query. Is there another way with SQL to approach this,
or should I be thinking more along the lines of a VBA macro that sends a
query to table3 to build the final query? (I do not even know if this is
possible)

Dave

 

[John Spencer (MVP)]

This looks like a crosstab query to me. Have you tried that?

UNTESTED SQL statmement follows

TRANSFORM FIRST(T3.FOODSHORT) as ItemName
SELECT T1.Name, T1.Address, T1.City, T1.Country
FROM (Table1 as T1 INNER JOIN Table2 as T2
ON T1.PID = T2.PID)
INNER JOIN Table3 as T3
ON T2.FID = T3.FID
GROUP BY T1.Name, T1.Address, T1.City, T1.Country
PIVOT T3.FOODSHORT

I am working with a database right now where I only have read-only ODBC
access, and I am using Access to send my queries. I have a main table that
has a one to many relationship with another table. That second table then
has a many to one relationship with a third table. What I want to do is take
the first one table, create another table with enough columns in the other
one file, and then left join the first table to the many table. ie:

Table1:
PID|Name|Address|City|Country

11|Dave|1237 This St|Anywhere|US
12|Chris|1236 This St|Anywhere|US
13|John|1235 This St|Anywhere|US

Table2:
PID|FID

11|1
12|1
13|1
11|2
13|2
12|3
11|4
12|4

Table3:
FID|FoodShort|FoodDesc

1|Cheese|Cheddar, tasty, melts well
2|Milk|Calcium rich, 250ml is one serving
3|Beer|A hearty, pale Ale
4|Pizza|Helps increase waist size

Now I want it to look like this:

View:
PID|Name|Address|City|Country|Cheese|Milk|Beer|Pizza

11|Dave|1237 This St|Anywhere|US|Cheese|Milk|Null|Pizza
12|Chris|1236 This St|Anywhere|US|Cheese|Null|Beer|Pizza
13|John|1235 This St|Anywhere|US|Cheese||Milk|Null|Null

I build that table using this query:

SELECT q01.*,q02.FoodShort as Cheese,q03.FoodShort as Milk,q04.FoodShort as
Beer,q05.FoodShort as Pizza
FROM ((((select P.* from Table1 as P) as q01
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=1]. AS q02
ON q02.pid = q01.PID)
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=2]. AS q03
ON q03.pid=q01.pid)
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=3]. AS q04
ON q04.pid=q01.pid)
LEFT JOIN [select PC2.PID,PC3.FoodShort from Table2 as PC2 INNER JOIN Table3
as PC3 ON PC2.FID=PC3.FID WHERE PC2.FID=4]. AS q05
ON q05.pid=q01.pid;

The only issue is that if table3 ever changes then the gig is up. If cheese
get's deleted, and then added again in with an FID of 5 (the good thing is
that referential integrity is maintained in this database, and all the FID
of 4 would disappear from table2) then my query "breaks" as all cheese would
show as null for this query. Is there another way with SQL to approach this,
or should I be thinking more along the lines of a VBA macro that sends a
query to table3 to build the final query? (I do not even know if this is
possible)

Dave

 

[Dave R.]

This looks like a crosstab query to me. Have you tried that?

UNTESTED SQL statmement follows

TRANSFORM FIRST(T3.FOODSHORT) as ItemName
SELECT T1.Name, T1.Address, T1.City, T1.Country
FROM (Table1 as T1 INNER JOIN Table2 as T2
ON T1.PID = T2.PID)
INNER JOIN Table3 as T3
ON T2.FID = T3.FID
GROUP BY T1.Name, T1.Address, T1.City, T1.Country
PIVOT T3.FOODSHORT

Figures there would be an easier way, and unfortunately I just started using
Access and had yet to get to crosstab queries. Thank you very much!

Dave R.