|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.
|
|
#1
|
|||
|
|||
|
Hello people. This is my first post on Dev Shed, I hope someone can help me out.
My problem is this .... I have a query that searches a linked table with start and end dates for an event linked by the event's ID. My problem is, if the where clause finds multiple matches in the date range table it returns the event in multiple rows. The query is listed below. Forgive me if it's a bit clumbsy, I'm not what you'de call "well versed in SQL" and I'm pretty much flying by the seat of my pants until I get some time to learn how to do things properly. ----------------------------------------------- SELECT event.*, dates.*, (SELECT tblTown.TownName FROM tblTown WHERE event.TownID = tblTown.TownID) AS TownName, (SELECT tblEventCategory.CategoryName FROM tblEventCategory WHERE event.CategoryID = tblEventCategory.CategoryID) AS CategoryName FROM tblEvent event, tblEventDates dates WHERE (event.CategoryID > @locCategoryLow AND event.CategoryID < @locCategoryHigh) AND (event.TownID > @locTownLow AND event.TownID < @locTownHigh) AND (event.EventTitle LIKE @locKeyword OR event.[Description] LIKE @locKeyword OR event.VenueName LIKE @locKeyword OR event.AdditionalDetails LIKE @locKeyword) AND (event.EventID = dates.EventID AND (DATEADD(d,-20,dates.StartDate) <= @SearchDate AND DATEADD(d,+20,dates.EndDate) >= @SearchDate)) ----------------------------------------------- The last part of the WHERE clause is the secret (I think). Any help greatly appreciated. Thanks in advance webBear1000 |
|
#2
|
||||
|
||||
|
Code:
select distinct
, tblTown.TownName
, tblEventCategory.CategoryName
, event.EventID
from tblEvent event
inner
join tblEventDates dates
on event.EventID = dates.EventID
inner
join tblTown
on event.TownID = tblTown.TownID
inner
join tblEventCategory
on event.CategoryID = tblEventCategory.CategoryID
where event.CategoryID > @locCategoryLow
and event.CategoryID < @locCategoryHigh
and event.TownID > @locTownLow
and event.TownID < @locTownHigh
and (
event.EventTitle LIKE @locKeyword
or event.[Description] LIKE @locKeyword
or event.VenueName LIKE @locKeyword
or event.AdditionalDetails LIKE @locKeyword
)
and dates.StartDate <= DATEADD(d,20,@SearchDate)
and dates.EndDate >= DATEADD(d,-20,@SearchDate)
|
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Linked tables return more than one row |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|