Ask Your Question
0

Query design

asked 2018-08-19 18:36:52 +0200

bobde gravatar image

updated 2018-08-19 18:39:54 +0200

Ratslinger gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-08-19 21:25:34 +0200

bobde gravatar image

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

edit flag offensive delete link more

Comments

@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.

Ratslinger gravatar imageRatslinger ( 2018-08-19 23:17:02 +0200 )edit
0

answered 2018-08-19 18:59:38 +0200

Ratslinger gravatar image

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?

edit flag offensive delete link more

Comments

@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 gravatar imageRatslinger ( 2018-08-19 23:28:21 +0200 )edit
Ratslinger gravatar imageRatslinger ( 2018-08-19 23:29:44 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-19 18:36:52 +0200

Seen: 37 times

Last updated: Aug 19 '18