SQL Query Issues: Avoiding cartesian product and right JOIN syntax

[Olivier López]

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]

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]

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]

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]

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]

These are not duplicates. SDATA is different.

[Vigneswaran Marimuthu]

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.