c# generate barcode CH A PT ER 1 0 H O W TO A PPRO A CH A Q UERY in Font

Create PDF-417 2d barcode in Font CH A PT ER 1 0 H O W TO A PPRO A CH A Q UERY

Once we have the view AllTourInfo, it can be used in the same way as any other table in our queries. To find the names of men who have entered a Leeston tournament, we can use the view as shown in Listing 10-8.

SELECT DISTINCT LastName, FirstName FROM AllTourInfo WHERE Gender = 'M' AND TourName = 'Leeston'

The big picture approach assumes that you have decided how to combine the tables that will contribute to the query. Sometimes, you will think it is obvious that, for example, you need to join the tables. Other times, it may not be at all clear initially. In this section, we will look at some key words that often appear in questions and that can provide a clue about which relational operations you will need. If none of these help, remember that we still have the No Idea Where to Start section coming up!

And and also can be tricky words when it comes to interpreting queries, and we will consider this further in the next chapter. In this section, we will look at queries that have the idea of two conditions needing to be met simultaneously. Queries that require two conditions to be met fall into two sorts: those that can be carried out with a simple WHERE clause containing AND and those that require an intersection or self join. To decide if a query really needs two conditions to be met, I usually look at a naturallanguage statement and see if I can reword it with the word both connecting the conditions. Consider these examples: Find the junior boys. (Both a male and a junior Yes.) Find those members who entered tournaments 24 and 38. (Both tournaments Yes.) Find the women and children. (Both a female and a child No.) The last query is the one that can trick you. Although it contains the word and, the common interpretation of women and children doesn t mean someone who is both a female and a child (that is, a girl). Rather, the phrase means anyone who is either a female or a child (especially when populating lifeboats).

The diagram in Figure 10-8 is a way of visualizing whether a query needs two conditions to be met. It portrays the example about woman and children. It shows both the union (only one condition must be satisfied) and the intersection (both conditions must be satisfied) of the set of women and the set of children. It is worth thinking like this about your data to decide what you actually need to answer your query accurately.

a) Either condition (Women and children)

b) Both conditions ( Girls )

When two conditions must be met, we are looking at the intersection of two groups of data, as in the diagram in Figure 10-8b. If we have a query that requires an intersection, that doesn t necessarily mean we must use the INTERSECT keyword. I find the following question helpful in deciding what to do next: Do I need to look at more than one row to decide if both conditions are satisfied Consider the query to find junior boys. This is going to need the Member table. Can we look at a single row and determine if the member is both a junior and a boy As we can see in Figure 10-9, it is possible to determine both these conditions from a single row.

In this situation, we can use a simple select operation with the Boolean AND to check for both conditions. This is discussed in 2, and the SQL is shown in Listing 10-9.

Listing 10-9. Both Conditions Can Be Checked in a Single WHERE Clause SELECT * FROM Member m WHERE m.Gender = 'M' AND m.MemberType = 'Junior'

Now consider a different type of query. What about finding the members who have entered both tournaments 24 and 36 To do this, we need to look at the Entry table (probably joined with the Member table if we want the names). As we can see in Figure 10-10, we cannot check that a member has entered both tournaments by looking at a single row.