4 tables insert - mysql

Question

I want to insert by selecting @DistName and @ProvName from district and province

INSERT INTO
    users
VALUES
(
    '@UserName',
    '@email',
    '@Pass',
    '@Status',
    '@phoneNo',
    '@VillName',
    '@DistName',
    '@ProvName'
);

Answer 1

You can use the SELECT statement to get the fields from other tables like below.

INSERT INTO
  users
VALUES
(
  '@UserName',
  '@email',
  '@Pass',
  '@Status',
  '@phoneNo',
  '@VillName',
  (SELECT '@DistName' FROM TABLE district WHERE distID=1),
  (SELECT '@ProvName' FROM TABLE province WHERE ProvID=1)
);

Hope this helps.

Answer 2

Note: Just count the fields' number in the table users and your trying to insert values number...

IMHO, strange relationships. But how I've understood => U need to get VilllD for inserting to users from three tables => village & district & province. It should look like:

INSERT INTO
 users
VALUES
(
 '@UserName',
 '@email',
 '@Pass',
 '@Status',
 '@phoneNo',
 (SELECT v.VilllD FROM village AS v 
                    INNER JOIN district AS d
                      ON v.DistID = d.DistID
                    INNER JOIN province AS p
                      ON p.ProvId = d.ProvId
                    WHERE v.VillName = @Villname AND
                          d.DistName = @DistName AND
                          p.ProvName = @ProvName )
);