0

i'm using a webservice to add value inside my database, normaly my query should add only one row but actually it had multiple identical rows.

So here is my 2 tables : Application.sql

Details.sql

My query: 

INSERT INTO Details 
(SecurityKey, Name, URL, DateLog, Level, Message, Username, ServerName, Port, ServerAdress, RemoteAdress, Logger, Exception, ApplicationID) 
SELECT  
@SecurityKey, @Name, @URL, @DateLog, @Level, @Message, @Username,@ServerName, @Port, @ServerAdress, @RemoteAdress, @Logger, @Exception, @ApplicationID
FROM Details D
INNER JOIN Application A ON @SecurityKey = A.SecurityKey AND @ApplicationID = A.ID

And my webservice with @ApplicationID=14 and @SecurityKey="shSfnDOIPM": 

{
  "DateLog": "27/02/12",
  "Level": "danger",
  "Message": "ytry",
  "Username": "ghth",
  "ServerName": "zrzrzer",
  "Port": "80",
  "ServerAdress": "36zr",
  "RemoteAdress": "12z",
  "Logger": "rg",
  "Exception": "zr",
  "ApplicationID": 14,
  "Name": "zr",
  "SecurityKey": "shSfnDOIPM",
  "URL": "wwww",
}

Normaly with these informations my query should sort only one row but as you can see in the "Details" picture, it create many and many rows...

Thanks for helps

Improve this question
2
  • 2
    mysql <> sql server, please don't tag products not involved Commented Oct 24, 2018 at 12:41
  • Please stop using comma based Implicit joins and use Explicit Join based syntax Commented Oct 24, 2018 at 12:48

2 Answers 2

Reset to default
1

It looks like you are referencing two tables in your query but you are not joining them together. Possibly getting an cartesian product in the process.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

you're right, how should i remove this cartesian product?
See SadmirD's answer below.
0

You are utilizing INSERT INTO SELECT query, yet you don't issue any JOIN to connect these two tables (either implicit or explicit). When utilizing the select query that affects multiple tables, you need to have N-1 where statements to join them (implicit join), or N-1 of such JOINs, in order to avoid cartesian product. Cartesian product = "multiple inserts" in your case, due to INSERT INTO... SELECT

I guess you need an INNER JOIN and would advise you to avoid implicit joins in general.

An example, editing your query:

INSERT INTO Details 
(SecurityKey, Name, URL, DateLog, Level, Message, Username, ServerName, Port, ServerAdress, RemoteAdress, Logger, Exception, ApplicationID) 
SELECT  
@SecurityKey, @Name, @URL, @DateLog, @Level, @Message, @Username,@ServerName, @Port, @ServerAdress, @RemoteAdress, @Logger, @Exception, @ApplicationID
FROM Details D
INNER JOIN Application A ON D.ApplicationID = A.ID 
WHERE A.SecurityKey = @SecurityKey AND A.ID = @ApplicationID

Notice that I put INNER JOIN and moved your previous "join" into a WHERE statement.

Improve this answer

4 Comments

I took your query but it again created multiples rows :/
"Multiple inserts" are also possible again, since you are doing INSERT INTO... SELECT. However, now you shouldn't have "identical entries within the same insert statement. This query will actually copy previous entries from table details that match the where clause and then insert them into same table.
But how should I get only one row, without the duplication of identical rows, because the webservice should give me only one return.
if you receive a DETAIL object that you need to save into your table, then you need to issue INSERT INTO DETAIL VALUES(var1, var2, var3), etc. If you still want to issue insert only for the first value found via INSERT INTO... SELECT, then you need to add LIMIT 1 at the end of your select query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.