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
LVL 10
jaggernatAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi J,

Based on the queries in other recent posts, this one is a snap.   :)

First, we'll join the tables as we've done before.  Since ProdID is one of the items that we're using for the query, we should probably OUTER join starting with the table that contains ProdID.  An INNER join is fine, but an OUTER seems more appropriate.

SELECT *
FROM offer_version_prod
LEFT OUTER JOIN offer_version
  ON offer_version_prod.OfferID = offer_version.OfferID
 AND offer_version_prod.Versionnummber = offer_version.Versionnumber
LEFT OUTER JOIN offer
  ON offer_version.OfferID = offer.OfferID
LEFT OUTER JOIN package
  ON offer.packageID = package.packageID;

Then simply apply the WHERE filter to select those rows that you want.


Kent
0
 
mankowitzCommented:
SELECT * FROM
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



0
 
jaggernatAuthor Commented:
thanks, do you think using joins would be a better idea?
thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
jaggernatAuthor Commented:
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.Versionnummber = offer_version.Versionnumber
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
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

Hi J,

The inner join will tell you IF you have a row with the OfferID and PackageID in question, and if such a row does exist return it for you.

The outer join will tell you IF you have OfferID (in the offer_version_prod table).  If you also have the corresponding row in the Package table (so that an inner join will return a row) the fields of the result set from the package table will be populated.

The outer join returns a row whenever the OfferID is valid.  The contents of the row tell you whether there is a package, and if not will tell you which join failed by the presence of a NULL key value in the result.



Kent
0
 
mankowitzCommented:
commas are syntactically equivalent to inner joins. It's just easier to write that way. The db will optimize the sql for you.
0
 
mankowitzConnect With a Mentor Commented:
Right, so if you join two tables which share a common field called ID, then an inner join will return the records where both tables have identical id. An outer join will show records where EITHER table has the id. See http://en.wikipedia.org/wiki/Join_(SQL)
0
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:

The commas make it easier to write, but much harder to understand (since the join conditions are not located with the joins).

For clarity sake, I find it best to use the modern join syntax.

-- DaveSlash
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

>> An outer join will show records where EITHER table has the id.

Not necessarily.  That requires a FULL outer join, something that isn't used nearly as often as the INNER join or LEFT/RIGHT OUTER join.



>> For clarity sake, I find it best to use the modern join syntax.

Absolutely.

As queries become more complex, it also becomes more important to use a consistent coding style.  Proper indentation is a must, and the query is much more readable when consistent usage of upper/lower case.  I like to see the reserved words capitalized (a throwback to when I worked in CYBIL) but a lot of people was to see the user object names in CAPS.  Some like mixed case.

Whichever you do, be consistent.  A good text editor that syntax hightlights keywords (like Textpad) is worth having, too.


Kent
0
All Courses

From novice to tech pro — start learning today.