Click here to Skip to main content
14,694,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I need to make a INNER JOIN inside this statement how does I do that??

This is what I have tried so far?

When I send a e-mail to user I want to display Monday instead of 1, so thats why I have to INNER JOIN dage table.


CREATE TABLE [dbo].[ordre_linie] (
    [ordre_linie_id] INT          IDENTITY (1, 1) NOT NULL,
    [antal]          VARCHAR (50) NOT NULL,
    [navn]           VARCHAR (50) NOT NULL,
    [fk_ordre_id]    INT          NOT NULL,
    [fk_kurve_id]    INT          NOT NULL,
    [total_pris]     VARCHAR (50) NOT NULL,
    [fk_dag_id]      INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([ordre_linie_id] ASC)
);

CREATE TABLE [dbo].[dage] (
    [dag_id]   INT          IDENTITY (1, 1) NOT NULL,
    [dag_navn] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([dag_id] ASC)
);



cmd.CommandText = @"INSERT INTO ordre_linie (SELECT * FROM ordre_linie INNER JOIN dage ON ordre_linie.fk_dag_id = dage.dag_id) 
                                (fk_ordre_id, navn, fk_kurve_id, antal, total_pris, fk_dag_id, dag_navn) 
                               VALUES (@fk_ordre, @navn, @kurv, @antal, @pris, @dag, @dag_navn)";


Hope someone could help me

/Tina
Posted
Updated 20-Apr-20 2:03am
v2
Comments
IpsitaMishra 30-Oct-13 7:40am
   
If u r asking for the foramt here is it

INSERT INTO Table3(Col1,col2....)
VALUES(SELECT Col1,Col2 ...
FROM table1
INNER JOIN tabl2
WHERE Condition)

if u want the exact solution please provide all the table structure.
tina_overgaard 30-Oct-13 8:11am
   
I have updated my question
tina_overgaard 30-Oct-13 8:15am
   
CREATE TABLE [dbo].[ordre_linie] (
[ordre_linie_id] INT IDENTITY (1, 1) NOT NULL,
[antal] VARCHAR (50) NOT NULL,
[navn] VARCHAR (50) NOT NULL,
[fk_ordre_id] INT NOT NULL,
[fk_kurve_id] INT NOT NULL,
[total_pris] VARCHAR (50) NOT NULL,
[fk_dag_id] INT NOT NULL,
PRIMARY KEY CLUSTERED ([ordre_linie_id] ASC)
);

CREATE TABLE [dbo].[dage] (
[dag_id] INT IDENTITY (1, 1) NOT NULL,
[dag_navn] VARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([dag_id] ASC)
);
thatraja 30-Oct-13 7:44am
   
what's the purpose? give us more details please
tina_overgaard 30-Oct-13 8:12am
   
When I send email I just send the value 1 instead of Monday
thatraja 30-Oct-13 8:35am
   
Share the sample table data, update your question.
thatraja 30-Oct-13 10:08am
   
Is dage is a master table like pre-defined values which will be used for ordre_linie?
And you're inserting values only in ordre_linie table, right?
[no name] 30-Oct-13 8:04am
   
Always use stored procedure in place of such parameterized queries..
tina_overgaard 30-Oct-13 8:11am
   
I have not learn to use stored procedures
IpsitaMishra 30-Oct-13 8:38am
   
please take a look on this http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx
Member 14600877 5-Oct-19 3:52am
   
i want to create a table with id and date , if we enter the today date in date column then date column field fill automatically with the day name after entering the date , how to do this ? plz give me solution

IpsitaMishra gave one sample but I personally don't like using VALUES when you have to use JOINs in your insert statement. I would recommend

INSERT INTO Table1 (Field1, Field2)
SELECT a.Field1, b.Field2
FROM TableA a 
INNER JOIN TableB b ON a.ID = b.ID
   
Comments
tina_overgaard 30-Oct-13 9:23am
   
But I nees the values so it insert the values in the database
ZurdoDev 30-Oct-13 9:28am
   
I re-read your question and do not understand. Explain in English what you need.
tina_overgaard 30-Oct-13 9:59am
   
I am insert values in my table ordre_linie and send the information to my email.

But in my email there is stand 1 on fk_dag_id and I want to show what 1 stand for in the table dage.
ordre_linie table
fk_dag_id = 1

dage table
dag_id = 1
dag_navn = Monday

dag_id = 2
dag_navn = Thursday
ZurdoDev 30-Oct-13 10:06am
   
That's not clear. Where are you stuck?
tina_overgaard 30-Oct-13 10:15am
   
This is how my email is look when it send to me:

Ordre nr Antal Navn
609 14 Blommer
609 7 Blomkål
Total pris på ordre 149 kr.
Leveringsdag 1 ????????????????????????? This one I want to show Monday from the table dag_navn

TinaOvergaard
Damgårdsvej 4
2100Kbh
2568965
ZurdoDev 30-Oct-13 10:21am
   
OK. So use what I posted and just modify.

INSERT INTO Table1 (Field1, Field2)
SELECT b.Field2, @parameter, @somevalue, @whateveryouneed.
FROM TableA a
INNER JOIN TableB b ON a.ID = b.ID
tina_overgaard 30-Oct-13 10:30am
   
Can I do this??

INSERT INTO Table1 (Field1, Field2)
SELECT TableA.Field2, @parameter, @somevalue, @whateveryouneed.
FROM TableA
INNER JOIN TableA ON table1.id = TableA.ID
ZurdoDev 30-Oct-13 10:32am
   
You need table 1 in your select statement.

INSERT INTO Table1(Field1, Field2)
SELECT TableA.Field2, @parameter, ...
FROM Table1
INNER JOIN TableA ON Table1.id = TableA.ID
ZurdoDev 30-Oct-13 10:32am
   
Basically, just write a SELECT statement that gives you the data you want and then you can put INSERT INTO on top of it after you have your select statement working.
tina_overgaard 30-Oct-13 10:45am
   
INSERT INTO ordre_linie
(SELECT * FROM dage, fk_ordre_id, navn, fk_kurve_id, antal, total_pris, fk_dag_id, dag_navn)
INNER JOIN dage ON ordre_linie.fk_dag_id = dage.dag_id
VALUES (@fk_ordre, @navn, @kurv, @antal, @pris, @dag, @dag_navn)

It comes and say
Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
ZurdoDev 30-Oct-13 10:46am
   
Right. Follow the syntax I gave you.

INSERT INTO order_linie (fields you are inserting into)
SELECT ...
tina_overgaard 30-Oct-13 10:49am
   
Now it is the values

Incorrect syntax near the keyword 'VALUES'.
ZurdoDev 30-Oct-13 10:51am
   
LOOK AT MY SQL. THERE IS NO VALUES. Sorry, for shouting but you don't seem to be listening very well. :)
tina_overgaard 30-Oct-13 10:58am
   
I have done in you way, but I need the values
tina_overgaard 30-Oct-13 10:59am
   
This is your way:
INSERT INTO ordre_linie (fk_ordre_id, navn, fk_kurve_id, antal, total_pris, fk_dag_id)
SELECT * FROM dage, dag_navn, @fk_ordre, @navn, @kurv, @antal, @pris, @dag, @dag_navn
INNER JOIN dage ON ordre_linie.fk_dag_id = dage.dag_id
"

And I get this error:
Must declare the table variable "@fk_ordre".
Must declare the table variable "@navn".
Must declare the table variable "@kurv".
Must declare the table variable "@Antal".
Must declare the table variable "@pris".
Must declare the table variable "@dag".
Must declare the table variable "@dag_navn".
ZurdoDev 30-Oct-13 11:18am
   
So, declare your variables. You'll need to pass them in.
thatraja 30-Oct-13 9:34am
   
That's why I asked you sample data
tina_overgaard 30-Oct-13 10:00am
   
I am insert values in my table ordre_linie and send the information to my email.

But in my email there is stand 1 on fk_dag_id and I want to show what 1 stand for in the table dage.
ordre_linie table
fk_dag_id = 1

dage table
dag_id = 1
dag_navn = Monday

dag_id = 2
dag_navn = Thursday
This one is Helpful to us....

http://stackoverflow.com/questions/7040390/sql-insert-into-with-select-and-inner-join[^]


Hope :
---------------------
Vishnu Prajapati
   
CREATE Table #Temp(SON nvarchar(50),ProNo nvarchar(50))

INSERT INTO #TEMP
SELECT SOH.SalesOrderNumber,PP.ProductNumber FROM Sales.SalesOrderDetail SOD (NOLOCK)
INNER JOIN Sales.SalesOrderHeader SOH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product PP (NOLOCK) ON PP.ProductID = SOD.ProductID

SELECT * from #Temp

Drop TABLE #Temp
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900