This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Query Issues: Avoiding cartesian product and right JOIN syntax

 
Olivier López
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello People¡
I'm a true greenhorn on SQL, so hope to get some help from you.

I have two different tables, one of them is DATOS and the other one is TICKET:

DATOS



TICKET


I want to make a query to get CREATED, ROWREF, AHT and SDATA from those two tables, but this is what happens:

If I run "SELECT ID, CREATED, ROWREF, AHT, SDATA FROM DATOS, TICKET": The result is a cartesian product, lots and lots of results (about 750k) and a bunch of them are duplicated

I saw on the internet, that this cartesian result can be fixed by using "JOIN", but I was not really sure which JOIN should
I use and where, so I ran: "SELECT ID, CREATED, ROWREF, AHT, SDATA FROM DATOS NATURAL JOIN TICKET" : In this case, i got
less results, just about 688, but there still many duplicated values

Can you please help let me know whats the right way to query this?

Basically the only link that those two tables have, is ID and ROWREF, but I havent realized how to fix it yet =(



 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

You have to add your join conditions. If you say that ID and ROWREF are the link, then you have to tell your SQL query that ID has to be equal to ROWREF.
In your query "SELECT ID, CREATED, ROWREF, AHT, SDATA FROM DATOS, TICKET" , you can add join conditions in the WHERE clause of your SQL statement.
 
Olivier López
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot for your response¡

Do you mean something like: "SELECT ID, CREATED, ROWREF, AHT, SDATA FROM TICKET JOIN DATOS WHERE (ROWREF = ID)" ???

Actually, it partially works, but now im facing another problem jaja

For instance, what I want is to get is the total SUM of the AHT, when SDATA equals to MMS, so I did this:

SELECT ID, CREATED, ROWREF, AHT, SDATA FROM TICKET JOIN DATOS WHERE ((ROWREF = ID) AND (SDATA LIKE '%MMS%'))

And this is what i get:



As you can see, many of the AHT fields are duplicated, so I cant SUM the aht because is going to add the duplicated values.

I would love to be able to run something like "WHERE (DISTICT AHT)" but DISTINCT applies for all of the columns, and I
just need it for AHT.

Im going to continue playing with it, i feel im close to achieve it ;)



 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Olivier López wrote:...
Actually, it partially works, ...

What does that mean?

By the way, I wads refering to your query: "SELECT ID, CREATED, ROWREF, AHT, SDATA FROM DATOS, TICKET"
Have you tried to add a where clause to that one?

How about learning some SQL step by step, before going for advanced queries?
First learn to query on one table. Then learn how to group, filter. Then learn how to join tables.
SQL is easy, but only if you master the basics
 
Olivier López
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jan Cumps wrote:
Olivier López wrote:...
Actually, it partially works, ...

What does that mean?


It means that now im not getting those 750k results, im just getting about 680 of them, so the cartesian results
issue seems to be fixed, but now im trying to avoid those duplicate AHT values

Jan Cumps wrote:
Olivier López wrote:By the way, I wads refering to your query: "SELECT ID, CREATED, ROWREF, AHT, SDATA FROM DATOS, TICKET"
Have you tried to add a where clause to that one?



Yep, i tried, and I get this:



I achieved to avoid the cartesian product, now I just need to avoid those duplicates on the AHT ;)



Im aware that i need to check those sql books i have somewhere, but i need to make this query run today, so im kind of in a hurry jajaja

Thanks in advance for your help
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
These are not duplicates. SDATA is different.
 
Vigneswaran Marimuthu
Greenhorn
Posts: 24
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The rows are not duplicated. As JAN said SDATA are different. Moreover this will be the result of your condition that you gave after JOIN.


SELECT ID, CREATED, ROWREF, AHT, SDATA FROM DATOS, TICKET ---> If you give a query like this, it ll result in cartesian product only. It is similar to CROSS JOIN.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic