jaggernat
asked on
join independent tables
hi experts
I have four tables
OFFER (primary key is 'OfferID')
OFFER_VERSION (This table has two Keys i.e combination of 'OfferID' and 'Versionnumber')
OFFER_VERSION_PROD (This table has three keys i.e combination of 'OfferID' ,'Versionnumber' and 'ProdId')
PACKAGE (primary key is 'packageId')
OFFER and OFFER_VERSION_PROD tables are not connected directly but they are connected through OFFER_VERSION table
OFFER -> OFFER_VERSION -> OFFER_VERSION_PROD
OFFER is also connected to PACKAGE table i.e OFFER table has the column
'packageId'
The PACKAGE table is only connected to OFFER table and nothing else.
My requirment is I have to write a query where i have to retrieve record based on 'ProdId' and 'packageId'.
any idea how i can accomplish that.
thanks
I have four tables
OFFER (primary key is 'OfferID')
OFFER_VERSION (This table has two Keys i.e combination of 'OfferID' and 'Versionnumber')
OFFER_VERSION_PROD (This table has three keys i.e combination of 'OfferID' ,'Versionnumber' and 'ProdId')
PACKAGE (primary key is 'packageId')
OFFER and OFFER_VERSION_PROD tables are not connected directly but they are connected through OFFER_VERSION table
OFFER -> OFFER_VERSION -> OFFER_VERSION_PROD
OFFER is also connected to PACKAGE table i.e OFFER table has the column
'packageId'
The PACKAGE table is only connected to OFFER table and nothing else.
My requirment is I have to write a query where i have to retrieve record based on 'ProdId' and 'packageId'.
any idea how i can accomplish that.
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, do you think using joins would be a better idea?
thanks
thanks
ASKER
Ok,
so if i use inner join , i will have
SELECT *
FROM offer_version_prod
INNER JOIN offer_version
ON offer_version_prod.OfferID = offer_version.OfferID
AND offer_version_prod.Version nummber = offer_version.Versionnumbe r
INNER JOIN offer
ON offer_version.OfferID = offer.OfferID
INNER JOIN package
ON offer.packageID = package.packageID;
Is the above correct?
Can you tell me how Outer join is more appropriate than Inner join
thanks
so if i use inner join , i will have
SELECT *
FROM offer_version_prod
INNER JOIN offer_version
ON offer_version_prod.OfferID
AND offer_version_prod.Version
INNER JOIN offer
ON offer_version.OfferID = offer.OfferID
INNER JOIN package
ON offer.packageID = package.packageID;
Is the above correct?
Can you tell me how Outer join is more appropriate than Inner join
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
commas are syntactically equivalent to inner joins. It's just easier to write that way. The db will optimize the sql for you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OFFER AS O, OFFER_VERSION AS OV, OFFER_VERSION_PROD AS OVP, PACKAGE AS P
WHERE
O.offerid=OV.offerid AND O.offerid=OVP.offerid AND O.packageid=P.packageid AND
o.packageid = xxxxxx AND OVP.productid=yyyyyy