Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ElCapitan
Starting Member
28 Posts |
 Posted - 2008-12-04 : 11:36:19
|
| I have a question regarding using CROSS JOINS and a WHERE clause to return a resultset from two unrelated tables.Lets say I have two tablestblAnnouncement----------------------AnnouncementIDTitleBodyAmountOrderedLowAmountOrderedHightblOrder----------------------OrderIDUserIDDateOfOrderAmountOrderedetc. There is no natural join with these two tables but there is a rule that each record in tblAnnouncement will apply to a field in each record in tblOrder. I would like to view a list of Announcements based on the criteria of the rule. The rule is..."Show each Anouncement record where the AmountOrdered for each record in tblOrder is greater than AmountOrderedLow and less than AmountOrderedHigh"The Order records are filtered by a specific UserID so lets say there are 15 records in question in tblOrder.In most queries we know the filter criteria before execution and add the conditions in the WHERE clause then execute the sql. In this example, we don't know what the filter criteria is until the sql begins to execute. Each record in tblAnnouncement has two values that are used as a condition where that Announcement record only gets included in the resultset if one of the Order records for a particular User meets that condition.Because there is no natural join, I am using a CROSS JOIN with a WHERE clause to test the condition. So far I have...SELECT DISTINCT tblAnnouncement.* FROM tblAnnouncement CROSS JOIN tblOrder WHERE tblOrder.UserID=?AND (tblOrder.AmountOrdered>=tblAnnouncement.AmountOrderedLow AND tblOrder.AmountOrdered<=tblAnnouncement.AmountOrderedHigh)The source at http://msdn.microsoft.com/en-us/library/ms190690.aspx explains that..."...if a WHERE clause is added, the cross join behaves as an inner join"Also, the article...http://www.tek-tips.com/faqs.cfm?fid=4785...explains how a recordset gets built using INNER JOINS. From this short article, for me, the above query would follow the same process/execution plan. Of course I could be way off base here and because of that any help and discussion would be greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-04 : 11:42:35
|
did you mean this?SELECT DISTINCT tblAnnouncement.* FROM tblAnnouncement INNER JOIN tblOrder ON tblOrder.AmountOrdered BETWEEN tblAnnouncement.AmountOrderedLow AND tblAnnouncement.AmountOrderedHigh |
 |
|
|
ElCapitan
Starting Member
28 Posts |
Posted - 2008-12-04 : 12:04:47
|
| I don't know, but I will now Google 'conditional joins' as it is something I have obviously missed.So, to continue with your suggestion, is this an efficient way of querying the database? Also, how far can you go with conditional joins, e.g. can you do...INNER JOIN tblOrder ON bla>blo AND blu=bli?Thanks again for your time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-04 : 12:09:38
|
quote:
Originally posted by ElCapitan
I don't know, but I will now Google 'conditional joins' as it is something I have obviously missed.So, to continue with your suggestion, is this an efficient way of querying the database? Also, how far can you go with conditional joins, e.g. can you do...INNER JOIN tblOrder ON bla>blo AND blu=bli?Thanks again for your time.
does that mean you dont what you need as result? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-12-04 : 12:46:09
|
quote:
Originally posted by ElCapitan
I don't know, but I will now Google 'conditional joins' as it is something I have obviously missed.So, to continue with your suggestion, is this an efficient way of querying the database? Also, how far can you go with conditional joins, e.g. can you do...INNER JOIN tblOrder ON bla>blo AND blu=bli?Thanks again for your time.
Efficient? Maybe.. Depends on your indexing. Yes, you can go farther with the join as you have illustrated. |
|
|
|
ElCapitan
Starting Member
28 Posts |
Posted - 2008-12-05 : 04:05:02
|
| Thanks for your your help Lamprey. I can't believe I did not know about conditional joins. With your heads up and the following link, http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx, I can achieve what I need.Thanks again for your time. |
|
|
|
|
|
|
|
|
CROSS JOINS and a WHERE clause to return a results