join and insert statment

[mrhankey]

hi guys,

quick question. i have 2 tables.

Client Table: ClientID INT PK AI, ClientName VarChar

Folders table: FolderID INT PK AI, FolderName VarChar, client_ClientID INT

The issue i have is that i have created this virtual folder appliaction in the site and have created it now where when they register it creates a folder in the folders table called ClientDocs.

However there are 1000 existing clients who need this default foldername created associated to their clientID

how can i run an insert into the folders table with all the existing clientID's from the clients table to save them doing this manually themselves?

so clientid from cleints table must insert into folders table along with the default foldername 'ClientDocs'.

as i said i have it now when they register it creates this insert however i need to run a query on the database for the existing clients.

many thanks in advance.

[smantscheff]

How do you match existing client entries with existing folders?
If all folders have the same name, you can do it like that (basically an inverted left join):

insert into folders (foldername, client_id) 
select 'ClientDocs', client_id from clients
where not (client_id in (select client_id from clients,folders where clients.client_id=folders.client_id))

[mrhankey]

thanks for the reply. i tried this:

INSERT INTO folders (foldername, ClientID) SELECT 'ClientDocs', ClientID FROM clients WHERE NOT (clients_ClientID IN (SELECT ClientID FROM clients,folders WHERE clients.ClientID=folders.clients_ClientID))

but i get unknown column in sub query in error.

where am i going wrong?

all clients that are existing need a default folder with the same foldername 'ClientDocs'.

thanks again

[smantscheff]

clients_ClientID is not a column.

[finito]

Look at this part alone:

SELECT 'ClientDocs', ClientID FROM clients WHERE NOT (clients_ClientID IN (SELECT ClientID FROM clients,folders WHERE clients.ClientID=folders.clients_ClientID))

In your first post you said:

Client Table: ClientID INT PK AI, ClientName VarChar

Look at this part:

WHERE NOT (clients_ClientID IN

You are calling for clients_ClientID from the Client Table Where you have said that client_ClientID is from the Folders Table.

[tinymark]

Give this a try.

INSERT INTO Folders (Foldername, client_ClientID) SELECT 'ClientDocs', ClientID FROM Client WHERE NOT (client_ClientID IN (SELECT ClientID FROM Client, Folders WHERE Client.ClientID=Folders.client_ClientID))

Your tables

Client Table: ClientID INT PK AI, ClientName VarChar
Folders table: FolderID INT PK AI, FolderName VarChar, client_ClientID INT

I don't see s ClientsDocs anywhere so I am going on faith.

[mrhankey]

thanks ClientDocs is the name of the folder i need to add into the folders table for every client.

new clients that register it inserts automatically, however i need to do this for the existing clients in the database.

thnaks again

[tinymark]

Ok I misunderstood here. Let's see if I have it right now. You have a client database with two tables in. New clients have the directory stored automatically with the new configuration. Old clients have a folder named ClientDocs that needs to be added to the table under the FolderName column. All clients use the same folder name ClientsDocs.

Do I have it now? Because we need a loop for this. And if the folder name is different for each client then we need some php and some input file to get the different names from.

[mrhankey]

thanks tinymark, yes that is correct.

each new client it automatically inserts into the folders table a default folder called "ClientDocs" with storing their ClientID in the folders table under clients_ClientID

so yes need it to run on the database for the existing clients that are already added to create a folder in the folders table storing their ClientID in the clients_ClientID in folders table.

thanks again

[tinymark]

Give this a try.

update `Folders` set `FolderName`='ClientDocs' where `FolderName`!='ClientDocs'

It tested on a 4 entry table with 2 blank ClientDocs. I hope I have it right this time.

[mrhankey]

thanks tinymark, however i need it to get all the ClientID from the clients table and insert their id into the folders table under clients_ClientID

thanks again

[tinymark]

;)You see, I didn't get it. Do you still need help?

[mrhankey]

yeah mate, really struggling with this to be honest. i made changes to the site and cant for life of me see a way of doing it. hopefully you can help :)

[tinymark]

OK, I don't know why I'm being so thick.;) Let's tackle the problem in a different way that I know will work. Do a dump of the database. You end up with an insert table definition with all the conditions built in. Parse that little fellow with a regex and change the values. Reload the database and presto chango.

Give that a try. If you have trouble with the php or jquery let me know.