3 replies to this topic

[Vaielab]

Hello,

I would like to create something like this

insert into tableA values(idTableB) inner join tableB on tableA.idTableB = tableB.idTableB where columnsFromB = 3

In a other word, only inserting data into tableA when the condition to table B return true from a join.

Do you know a way to do so?

[WingedPanther]

Can you show some sample data, I'm far from clear on what you're trying to do.

[Vaielab]

Ok, I'm gonna try to use a better example.

Table user and table state (state as province, or territory of a country)
And the table state have a id_country in it

I want to insert a new user.
The user has to have a state (id_state)
But I want to insert the user only if the state have a certain country

So something like that
insert into user(...) values(...) inner join state on user.idState = state.idState where state.idCountry = 3

In a other word, I want to insert the data only if a certain condition from a joined table return true

Sorry for not using real data, but my guess is if I use real data, it will only be more complicated

[WingedPanther]

My experience is that real data makes it less complicated.

It sounds a lot like you should be doing a query to check, and if the check passes, then do the insert statement. Think about it this way: if it's not valid, won't you want to display an error to the user? Your above idea would not work. Alternatively, you might be able to use a foreign key constraint so you'd have a basic insert statement, and rely on the FK to throw an error.

The only way I can think of where you might be able to pull this off would be the following:

INSERT INTO user (f1, f2, f3) SELECT DISTINCT '$val1', '$val2', '$val3' from state where idState='$idState' and idCountry=3;

The idea here is to hardcode the values you want to insert using an insert-select statement, but limit the rows based on the other conditions. Not sure if that would work or not. I tossed in a DISTINCT in case there's more than one match in table state.