sql subquery problem multiple tables

[antonyx]

ok we have 3 tables campaign...campaignteam, and staff
we want to show the staff details by what campaign they are in

insert into CTeam
values ('001','20','10','11','12','13');


here is how we enter staff members into campaigns

001=CTeamId
20=CampaignId
10,11,12,13=StaffIds

the fields for the campaign team are CManager,Contact1,Contact2 and PurchasingAst

we tried this query

SELECT stForname,stSurname,stTelNumber
FROM Staff
WHERE StaffId = (SELECT CManager,
FROM CTeam WHERE CTeam.CTeamId='001')

AND StaffId = (SELECT Contact1,
FROM CTeam WHERE CTeam.CTeamId='001')

AND StaffId = (SELECT Contact2,
FROM CTeam WHERE CTeam.CTeamId='001')

AND StaffId = (SELECT PurchasingAst,
FROM CTeam WHERE CTeam.CTeamId='001');

it dont work, its wrong and we dont know how to fix it.for this example we have just used the CTEAM id of 001 to try and load the staff members in that team. any help?

 

[r937]

could you please show the CREATE TABLE statement for the 3 tables, as well as a few sample rows of data from each table, and then indicate what you waht the query results to look like

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[fredericofonseca]

From your example you have a badly designed table.

e.g. you are stating that your table is
CTeam (
CTeamId,
CampaignId,
StaffId1,
StaffId2,
StaffId3,
StaffId4
)
where StaffId(1-4) are all identifiers of the same entity (staff)

Your SQL is also wrong even if we ignore the above error, as it is using a AND instead of an OR which means that in order for a record to be retrieved the 4 staffIds from CTeam would need to be all the same.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[antonyx]

ok, i shall post the table designs shortly.
in reality we need to show that the campaign team has four members in it and we dont know how else to do that in table form. we thought it looked a bit redundant but we dont konw any other approach

 

[fredericofonseca]

If you need four members then you have 4 records per team. Thats how a data should be organized.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[antonyx]

so use a dif table for each campaign?

 

[fredericofonseca]

NO!!!!

Each campaign will be identified by a unique reference.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[antonyx]

me a gimp, me no understand

 

[fredericofonseca]

Lets get a analogy.

Thing of a flight and it´s seats.

Each flight as a aircraft assigned to it.
Each aircraft has a certain number of seats that can be allocated to passengers.
Also each aircraft can have 1 or more pilots (normaly 2).
Also the same flight number can occur on different days, and eventually can have different aircraft models each day.

If you needed to hold the information related to each flight would you create one table for each individual day? No. You would hold a table with a date and a flight number as the key.
The same would apply to the passenger allocation. If you were to create a individual item on a table for each individual passenger (which was what you were designing on your original table), then you would have a table with 500 items (passenger 1, passenger2, ... passenger500), which would be unworkable. So what you do is you create another table with flight information, and with seat added to the key so you would know who was on what seat on what flight on which day.

Now transpose this to your particular case and please post what you think would be the correct table layout on your case, making sure you highlight the relevant unique keys of each table.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com