Multiple LEFT JOIN from two tables causing migrane

[ssgagosto]

I have multiple tables I'm going to be putting into a query, but I'm getting stuck on one thing.

I have a main table called 'tblEvent' with data similar to the following:

eventID | eventCheck1 | eventCheck2
123 | 0 | 0
124 | 1 | 0
125 | 0 | 1
126 | 1 | 1

...which is related to a table called 'tblEventCheck' displaying the following data:

eventCheckCheckedYes | eventCheckDesc1 | eventCheckDesc2
1 | Description1 | Description2

For the problem I'm having with this particular query, I need to be able to select all the data from 'tblEvent' and display it, whether or not all the 'eventCheckDesc's' are empty (because there are a ton of other things in the table that will display outside of this issue).

Here's what I got from playing around with some queries:

SELECT tblEvent.eventID, tblEvent.eventCheck1, tblEvent.eventCheck2, tblEventCheck.eventCheckCheckedYes, tblEventCheck.eventCheckDesc1, tblEventCheck.eventCheckDesc2
FROM tblEvent LEFT JOIN tblEventCheck ON (tblEvent.eventCheck1 = tblEventCheck.eventCheckCheckedYes) AND (tblEvent.eventCheck2 = tblEventCheck.eventCheckCheckedYes)

Giving me the following output:

eventID | eventCheck1 | eventCheck2 | eventCheckDesc1 | eventCheckDesc 2
123 | 0 | 0 | |
124 | 1 | 0 | |
125 | 0 | 1 | |
126 | 1 | 1 | Description1 | Description1

...This is not what I want because the 'AND' in the SQL statement only displays if both match.
I need it to look like this for the output:

eventID | eventCheck1 | eventCheck2 | eventCheckDesc1 | eventCheckDesc 2
123 | 0 | 0 | |
124 | 1 | 0 | Description1 |
125 | 0 | 1 | | Description2
126 | 1 | 1 | Description1 | Description1

I have not been able to find anything out on the web with this particular issue. I've tried playing around with wizard with no avail, as well as changing the nested LEFT JOIN's without using the 'AND' or 'OR' statement to no avail.

Someone please help! Thanks in advance for anyone you can give me a swift kick in the right direction.

By the way I'm using Access 2007.

Thanks

 

[LPurvis]

I'f I'm interpreting what yu're saying correctly, then you seem to have a real design flaw as well as querying problems.

Are you saying that rows in 'tblEventCheck' have a PK (eventCheckCheckedYes) which is entered as the FK in either eventCheck1 or eventCheck2 in 'tblEvent'?

And in each case two columns data are stored?
i.e. eventCheckDesc1 refers to eventCheck1 data and eventCheckDesc2 to eventCheck2?

Even if eventCheck1 and eventCheck2 are inextricably related (i.e. always come in pairs - is there a definite business rule for that requirement?) - the result is this convoluted querying requirement.
(i.e. based on the data it certainly doesn't look like this is a business rule that doesn't deserve a more proper design).

Given the multiple occasions the same row is referenced, it looks like 'tblEventCheck' holds some sort of standard description records (but given that eventCheck1 or eventCheck2 may be entered that pairing them is even more crazy - as only one of the descriptions applies at the same time?)


tblEvent
eventID (PK)

tblEventCheck
eventCheckID (PK)
eventID (FK)
eventCheckDescriptionID (FK)
eventCheckedNo (1 or 2 - or more if the need arises)

tblEventCheckDescription
eventCheckDescriptionID (PK)
eventCheckDescription


Related such as:
tblEvent 1---N tblEventCheck N---1 tblEventCheckDescription

Making the same example data look like:

[B]tblEvent[/B]
eventID 
--------
123 
124
125 
126  
 
[B]tblEventCheck[/B]
eventCheckID  eventID  eventCheckDescriptionID  eventCheckedNo 
--------------------------------------------------------------
1             124      10                       1
2             125      11                       2
1             126      10                       1
2             126      11                       2
 
[B]tblEventCheckDescription[/B]
eventCheckDescriptionID  eventCheckDescription
----------------------------------------------
10                       Description1
11                       Description2

All based on approximate understanding of what you have.

Queried directly as

SELECT * 
FROM 
  (tblEvent E
    INNER JOIN 
  tblEventCheck C ON E.eventID = C.eventID)
    INNER JOIN
  tblEventCheckDescription D ON C.eventCheckDescriptionID = D[B].[/B]eventCheckDescriptionID

As Outer Joins shouldn't be required. (Records only exist as required).
You can pair the records back up if you need to - but the data is all there.

In your current situation - you just need to "OR" instead of "AND" the criteria in the Join. :-s

Cheers.

 

[ssgagosto]

I'f I'm interpreting what yu're saying correctly, then you seem to have a real design flaw as well as querying problems.

Are you saying that rows in 'tblEventCheck' have a PK (eventCheckCheckedYes) which is entered as the FK in either eventCheck1 or eventCheck2 in 'tblEvent'?

Yes

And in each case two columns data are stored?
i.e. eventCheckDesc1 refers to eventCheck1 data and eventCheckDesc2 to eventCheck2?

Yes

Even if eventCheck1 and eventCheck2 are inextricably related (i.e. always come in pairs - is there a definite business rule for that requirement?) - the result is this convoluted querying requirement.
(i.e. based on the data it certainly doesn't look like this is a business rule that doesn't deserve a more proper design).

Because of the way I designed it, it will be easier for me to make a form to edit the eventCheckDesc1, 2, etc.

Given the multiple occasions the same row is referenced, it looks like 'tblEventCheck' holds some sort of standard description records (but given that eventCheck1 or eventCheck2 may be entered that pairing them is even more crazy - as only one of the descriptions applies at the same time?)

I know it sounds nuts, but in tblEvent, I only care if there's a '1' in the field for the eventCheck1, 2, etc. Any other number is irrelevant. But it works for my application. I'm a beginner, a real fresh one!

tblEvent
eventID (PK)

tblEventCheck
eventCheckID (PK)
eventID (FK)
eventCheckDescriptionID (FK)
eventCheckedNo (1 or 2 - or more if the need arises)

If it's not checked, then I don't need a 'No' field.

tblEventCheckDescription
eventCheckDescriptionID (PK)
eventCheckDescription

Related such as:
tblEvent 1---N tblEventCheck N---1 tblEventCheckDescription

Making the same example data look like:

[B]tblEvent[/B]
eventID 
--------
123 
124
125 
126  
 
[B]tblEventCheck[/B]
eventCheckID  eventID  eventCheckDescriptionID  eventCheckedNo 
--------------------------------------------------------------
1             124      10                       1
2             125      11                       2
1             126      10                       1
2             126      11                       2
 
[B]tblEventCheckDescription[/B]
eventCheckDescriptionID  eventCheckDescription
----------------------------------------------
10                       Description1
11                       Description2

All based on approximate understanding of what you have.

Queried directly as

SELECT * 
FROM 
  (tblEvent E
    INNER JOIN 
  tblEventCheck C ON E.eventID = C.eventID)
    INNER JOIN
  tblEventCheckDescription D ON C.eventCheckDescriptionID = D[B].[/B]eventCheckDescriptionID

As Outer Joins shouldn't be required. (Records only exist as required).
You can pair the records back up if you need to - but the data is all there.

In your current situation - you just need to "OR" instead of "AND" the criteria in the Join. :-s

Cheers.

Unfortunately, the OR does not give me the desired result, per my tests using the LEFT JOIN and especially not INNER JOIN as I need ALL the results to display.

However, I did finally get this resolved with help from another source.

The cure to my issue is as follows giving me the desired result:

    select e.*, (select eventCheckDesc1 from tblEventCheck where eventCheckCheckedYes = e.eventCheck1 and e.eventCheck1 = 1) as EventCheckDesc1, (select eventCheckDesc2 from tblEventCheck where eventCheckCheckedYes = e.eventCheck2 and eventCheck2 = 1) as EventCheckDesc2 
from tblEvent e

...which pulls all the data from my tblEvent (which I will be using later anyways) and displays all rows as I require.

My problem has been resolved, but I do appreciate you getting back to me.

Thanks

 

[LPurvis]

Hi.

Just a few commments - intended in good spirit.

>> Because of the way I designed it,
Exactly. That's what you're fighting here - and the entire point of my post.
There are more correct designs. What I offered is only one possibility.

>> I'm a beginner, a real fresh one!
That's absolutely understandable. However you won't always be one. At some stage you need to start designing databases more properly - or you'll forever be plagued with problems and requiring convoluted querying solutions (if possible at all via a query).

>> If it's not checked, then I don't need a 'No' field.
I should have been more clear with the naming convention. That would stand for "Number" and as I indicted, it could have a value of 1 or 2 depending upon which type of EventCheck you were performing.
It's a completely different model that was proposed.

>> Unfortunately, the OR does not give me the desired result
Well, that depends upon what your desired result is.
I see based on your original question you actually did only want the appropriate Description to appear based on which EventCheck was entered. (The formatting loss of the question meant I missed that first time out).
I'm sorry but this just further re-enforces how inappropriate your design is.
Wanting one or other of the columns to return is classic design flaw.

>> However, I did finally get this resolved with help from another source.
I'd be curious to hear what source that was. Another forum?
If they've just fed out the answer without any voice of concern on the schema then they have done you a disservice. Even if you ignore the advice and stick with the (what will eventually become a poorly performing) query, you should be aware that it is not a strong solution.

>> My problem has been resolved
Well, I'd have to beg to differ on that score. ;-)

I think you would benefit from reading up on normalisation. For future reference if nothing else.

Oh go on then.
Though I don't want to linger on a solution with that design... This is, at least, a better query than the one you've been given.

SELECT E.eventID, E.eventCheck1, E.eventCheck2, C1.eventCheckDesc1, C2.eventCheckDesc2
FROM (tblEvent E
    LEFT JOIN 
  tblEventCheck C1 ON E.eventCheck1 = C1.eventCheckCheckedYes)
    LEFT JOIN 
  tblEventCheck C2 ON E.eventCheck2 = C2.eventCheckCheckedYes

Cheers.