An issue with joining tabels.

[#1]

I have two tables

For one of the Episodes of Sienfield

First table has this info

Title Name, Episode Number, Title ID, Actor Info, Amt. owed to Distributor, Length of the Show etc.,

second table has the following info

Title ID, Release Number

I am trying to join these tables to get the Release Number from the second table.

Why is it listing all the rows from the second table even though the join automatically happens on the TItle ID?

Table 1 data

Sienfield 1, 1 100,xxxx,1000, 30

Sienfield 2, 2 100,tyty,1000, 30

Sienfield 3, 3 100,hggh,1000, 30

Sienfield 4, 4 100,xxxxx,1000, 30

Sienfield 5, 5 100,xxxx,1000, 30

Sienfield 6, 6 100,xxxx,1000, 30

Table 2 data

100, 1999

101, 2000

102,2012

103,2003

When I join on TItle ID, I am expecting the release year to come as 1999. But it is displaying all the release years.

This is how I am coding this. I thought the join would happen autoamatically on the titleid and 1 row will be returned for the releaseyear of title 100. Can some one help me what the problem is

title:

LOAD
    "TITLE_ID" as titleid,

     titlename,

amt

;
SQL SELECT "titleid","titlename",amt
FROM  "T_TITLE_info";

releaseyear:

LOAD "RELEASE_YEAR" as releaseyear,

    "TITLE_ID" as titleid;

SQL SELECT "RELEASE_YEAR",

    "TITLE_ID"

FROM  "T_TITLE";

[MayilVahanan]

Hi

Use left join. For example:

Load * Inline

[

TitleName, EpisodeNumber, TitleID, ActorInfo, Amt.owedtoDistributor, Length

Sienfield 1, 1, 100,xxxx,1000, 30

Sienfield 2, 2, 100,tyty,1000, 30

Sienfield 3, 3, 100,hggh,1000, 30

Sienfield 4, 4, 100,xxxxx,1000, 30

Sienfield 5, 5, 100,xxxx,1000, 30

Sienfield 6, 6, 100,xxxx,1000, 30

];

Left join

LOAD * Inline

[

TitleID,Year

100, 1999

101, 2000

102,2012

103,2003

];

in this Year gives 1999 alone..

Regards,

R.MayilVahanan

[CELAMBARASAN]

You mean you don't want to load the Non existing Title ID's?

title:

LOAD
    "TITLE_ID" as titleid,

     titlename,

amt

;
SQL SELECT "titleid","titlename",amt
FROM  "T_TITLE_info";

releaseyear:

LOAD "RELEASE_YEAR" as releaseyear,

    "TITLE_ID" as titleid Where Exists(titleid,TITLE_ID);

SQL SELECT "RELEASE_YEAR",

    "TITLE_ID"

FROM  "T_TITLE";

[jldengra]

The join does not "automatically happen" as you say.

Your tables are being joined by the field Title ID, not by inner join but by outer join, so QV is displaying all the release years, even not related to any record in the model.

In order to make an INNER join, you need to do it explicitly into your script.

Let be EPISODES the name of the first table you load, then you need to state INNER JOIN (EPISODES) before the load of the related release years in your script:

EPISODES:

LOAD ....

.......;

INNER JOIN (EPISODES)

LOAD .......

.........;

I hope this helps.

Greetings,

J.L.Dengra

[#5]

Dengra, Thanks for your response.

I have my code in qvw as follows

EPISODES:

LOAD ....

SELECT title_id,title_name,amt, episode_no from t_itle

RELEASENUMBER:

LOAD

SELECT titile_id,release_number rom t_release

Store EPISODES into QVDGenerator\episodes.qvd;

Store RELEASENUMBER:into QVDGenerator\release.qvd;

I am confused as to where I can add the join. When I add it before the load under ReleaseNumber, I am getting an error. Can you please guide me through that? Thanks

[MayilVahanan]

HI

EPISODES:

LOAD *;

SELECT title_id,title_name,amt, episode_no from t_itle;

Inner Join(EPISODES)                                     // else Left Join()

LOAD *;

SELECT titile_id,release_number from t_release;

Store EPISODES into QVDGenerator\episodes.qvd(qvd);

Store RELEASENUMBER:into QVDGenerator\release.qvd(qvd);

Hope that helps

[#7]

Can some one please provide me the syntax for inner join in the below scenario? There are tables from two different databases. I only want to get the release_number from t_release table for the specified title_id in the first table and I am not sure where to add the join.

EPISODES:

LOAD ....

SELECT title_id,title_name,amt, episode_no from t_itle

RELEASENUMBER:

LOAD

SELECT titile_id,release_number rom t_release

Store EPISODES into QVDGenerator\episodes.qvd;

Store RELEASENUMBER:into QVDGenerator\release.qvd;

[jldengra]

I think you should only model a table: EPISODES, with its QVD, but not for RELEASENUMBER.

If you still need to keep a separate table for RELEASENUMBER, it is possible, but you would need to rename the fields to avoid a synthetic key error.

This is the resulting code modifying the sample from Mayil Vahanan Ramasamy , assuming that you need to store only the related releases, otherwise you should not include the last inner join: 

EPISODES:
Load * Inline
[
TitleName, EpisodeNumber, TitleID, ActorInfo, Amt.owedtoDistributor, Length
Sienfield 1, 1, 100,xxxx,1000, 30
Sienfield 2, 2, 100,tyty,1000, 30
Sienfield 3, 3, 100,hggh,1000, 30
Sienfield 4, 4, 100,xxxxx,1000, 30
Sienfield 5, 5, 100,xxxx,1000, 30
Sienfield 6, 6, 100,xxxx,1000, 30
];

RELEASES:
LOAD TitleID as TitleID_releases, Year as Year_releases Inline
[
TitleID, Year
100, 1999
101, 2000
102,2012
103,2003
];

INNER JOIN (EPISODES)
LOAD DISTINCT TitleID_releases as TitleID
RESIDENT RELEASES;

// Only if releases need to be limited to related to episodes
INNER JOIN (RELEASES)
LOAD DISTINCT TitleID as TitleID_releases
RESIDENT EPISODES;

[#9]

Should the inner join go under Episodes or ReleaseNumber tag?

[MayilVahanan]

HI

Please check the attached file .

Hope it helps