Query design

[bobde]

I have a database with 900+ members. Each member is assigned to three teams with different positions on each team. I’ve written a query that will extract each team member and position for that team put the results in a temporary table.
I’ve run the query in phpmyadmin, runs perfectly.
Tried to run the query in LO and I get a syntax error… Here is the query

TRUNCATE TABLE `team`
INSERT INTO team (ID, phone, first_name, last_name, position, status, notes)
SELECT t2.RosterID, t2.Phone_Number, t2.First_Name, t2.Last_Name, t1.Position, t1.Status, t1.Notes
FROM rosterinfo t1, memberpersonalinfo t2 WHERE (t1.RosterID=t2.RosterID) AND (t1.Team = 1) AND (t1.Position IS NOT NULL);

INSERT INTO team (ID, phone, first_name, last_name, position, status, notes)
SELECT t2.RosterID, t2.Phone_Number, t2.First_Name, t2.Last_Name, t1.Position2, t1.Status, t1.Notes
FROM rosterinfo t1, memberpersonalinfo t2 WHERE (t1.RosterID=t2.RosterID) AND (t1.Team2 = 1) AND (t1.Position2 IS NOT NULL);

INSERT INTO team (ID, phone, first_name, last_name, position, status, notes)
SELECT t2.RosterID, t2.Phone_Number, t2.First_Name, t2.Last_Name, t1.Position3, t1.Status, t1.Notes
FROM rosterinfo t1, memberpersonalinfo t2 WHERE (t1.RosterID=t2.RosterID) AND (t1.Team3 = 1) AND (t1.Position3 IS NOT NULL);

Any Ideas ??
Bob

[Ratslinger]

Hello,

This is not really a Query. A Query is a selection of information. This is inserting data into a table using data selected from other tables. For most cases, the Query section of LO only allows Select statements. Creation of tables and inserting of data is not performed there but rather under the menu item Tools->SQL from the main screen.

It is also questionable as to the reason for this. Have you considered instead creating a View?

[Ratslinger]

@bobde The information provided is vague. It appears the question now turns to creating a Report. Is this correct? If so, it is all in using SQL properly to generate the necessary information. Don’t have data (sample would help here with a clear explanation of just what the real problem is) but it appears that all that is needed is your select statements with Union to create the need query.

[Ratslinger]

See → SQL UNION Operator.

[bobde]

Hi Ratslinger
I’m trying to get information from a table that contains details on each member. what team each member is assigned to and what position they hold on that team. Each member can be on ( up to ) three teams and hold up to three different positions . EX: Team: 1 Position: driver … Team:2 Position: dispatch.
The only way I’ve been able to make it work is to run the query for team 1 and put the results into a temp table, then run the query for team 2 and team 3
Then use that information to run a team report
Any ideas on how to extract the information??

Thanks
Bob

[Ratslinger]

@bobde This is not an answer. Please use comments or if needed EDIT original question and note edited information.

Answers should be for replying to original question only. You can answer your own question.