SQL Query Help

[Scott24x7]

Hi All,
I seem to remember from years ago when I was active in VFP and SQL, that you could have two (or more tables, but I'll keep it simple) tables, one with data, and one with a code. You could put something in your SQL statement so that you would get the "data" value of the "code" instead of the code.

For example:
Table 1 (Customers) has the following fields
FirstName
LastName
DOB
MemberType (a value 1 - 4)

Where MemberType is a value from 1 - 4
Table 2 (MemberTypes) has the following fields
MemberType (a value 1 - 4)
MemberCode

(Where MemberCode is)
"Active"
"Inactive"
"Prospect"
"Closed"

So what I want is a SQL (I think maybe this is a JOIN, but I'm very rusty) statement that in my resulting CURSOR I have the LASTNAME and the MEMBERCODE that is equivalent to the Customer MemberType.

How can I accomplish that in SQL?



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Tore Bleken]

[pre]Select Lastname, MemberType.Membercode from Customers inner join MemberType on Customers.MemberType = MemberType.MemberType into cursor curData[/pre]

 

[Olaf Doschke]

Yes, a join is the equivalent of a relation.

Maybe have a look at an sql tutorial, if you don't remember such essential and basic queries, you need a tutorial. SQL was in the language in 1995 in VFP6, but since you say you mainly did fpw 2.6...

When you would relate these tables you'd have an index on MemberTypes.MemberType (eg tab memtype) and set the relation from Customers to MemberTypes via:

SELECT MemberTypes
SET ORDER TO memtype
SELECT Customers
SET RELATION TO MemberType INTO MemberTypes
BROWSE FIELDS Customers.Lastname, MemberTypes.MemberCode

Well, what does that do? It looks up Customer.Membertype in the index memtype on MemberTypes.Membertype and positions at the matching record there, so it'll be on the browse result, or at eof, if no match is found.

A left join does the same, it's even more direct in what you specify, because you specify the direct matching criterion, not an index. You don't have to go through one more hoop of knowing which index is about which expression. The only need of an index is for rushmore optimization, but the sql engine determines what index to use, not you:

SELECT Customers.Lastname, MemberTypes.MemberCode;
FROM Customers ;
LEFT JOIN MemberTypes ON MemberTypes.MemberTypes = Customers.MemberTypes ;
INTO CURSOR crsResult

Indeed the same index as for the relation helps to make the join without a full table scan on the MemberTypes table, though a scan through 4 records isn't much. A rule of thumb is you crearte indexes to make On- and Where-Conditions a lookup in an index. The strength of this is with multiple join and where conditions even on the same table rushmore makes use of several indexes.

Bye, Olaf.

 

[vgulielmus]

One equivalent solution to Tore Bleken's, is :
Select Customers.Lastname, MemberType.Membercode from Customers,MemberType where Customers.MemberType = MemberType.MemberType into cursor curData
or even shorter
Select Lastname, Membercode from Customers,MemberType where Customers.MemberType = MemberType.MemberType into cursor curData

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

There's one main difference in my and Tore Blekens query: Left (Outer) vs Inner join. As said the left join resembles the RELATION, an inner join does something more, it strictly filters results to matches. If a relation goes to EOF in the target table you get an empty field in the BROWSE, if you do an outer join you get a NULL at that place and with an inner join the row will not be put into the result, so an inner join is a filter at the same time.

In my query you get all customers and you get membercodes, where known. If Customers have a Membertype NULL or 0 or even something >5, that would have no Membercode.
In Tores query you get only customers with correct MemberType.

Left Join means all of the left table and matching data of the right table, you can imagine what a RIGHT join means.

The inner join suppresses any half results, but there are obviously other instruments to prevent a Customer having no or incorrect MemberType info, eg Referential integrity rules with the check condition the Customer.MemberType has to exist in MemberTypes, also vice versa eg a rule for MemberTypes prevening deletion as long as a MemberTypes record is referenced somewhere the database knows relations exist.

These are the type of permanent relations in a DBC which result in the live relations you get when putting these tables into the form data environment.

If you have an MSDN subscription get SQL Server, if not take the Express variant and install the management studio. The table designer is pretty much intuitively usable in the same way as the VFP table designer. Once you have tables and relate them there, the query designer of the management studio will do the same as the VFP dataenvironment and make the relations in the visual canvas you add tables in the query editor. At the same time you get an SQL query with the right joins (Or left joins). It may do more joins than you want, especially in scenarios with many tables. It may also do more inner joins than you want, but you can cut relations and change their type, too. A nice way to get query code, if you're starting off with SQL.

Bye, Olaf.

 

[Olaf Doschke]

Select Lastname, Membercode from Customers, MemberType indeed also works quite simple. The where clause makes it an inner join, the syntax is shorter. If the where conditions are omitted, this is a cross join, a join type, which matches any pair of records, if not limited by further conditions. If that isn't rushmore optimzable it'll get very slow, but indeed the where condition turns this cross join to an inner join. Putting conditions in the where clause always enforces them. If you combine a left join with a where condition repeating the join condition or making a further condition on the right table and your condition doesn't allow NULLs, the outer join automatically becomes an inner join, so that's to be taken with caution. I recommend to make a distinction of conditions used for joining and for filtering data.

Bye, Olaf.

 

[vgulielmus]

I agree with this recommendation

I recommend to make a distinction of conditions used for joining and for filtering data.

It's cleaner and easier to maintain.

Still, it may have one advantage : sometimes the engine optimize better than yourself the order of the joins.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Well, let me give you an example, why it pays to rather be strict about this:

Assume you have a database with products and the main product record has optional info on its origin country (it may only be relavant for food products, for example).
Assume you solve this by making product.origincountryid NULLable.

If you join products and the origin countres, you do an outer join of the countries, as they may or may not be referred:

SELECT * FROM products prd LEFT JOIN origincountries oc ON oc.id = prd.origincountryid

In itself that already is a case where a cross join would fail listing any products without origin countries, if you do:

SELECT * FROM products prd, origincountries oc WHERE oc.id = prd.origincountryid

You might fix it with

SELECT * FROM products prd, origincountries oc WHERE oc.id = prd.origincountryid OR prd.origincountryid IS NULL

That may already convince you to using join conditions rather than WHERE in case you want an outer join. It may not convince enough in an inner join situation. I haven't tried, but in fact it makes no condition on oc in case prd.origincountryid IS NULL, so it may join all origin countries to such products instead of none. That may even differ from database to database.

Now comes a time your data moves to SQL Server and your customer wants to keep the VFP advantage of the DELETED() status of records to be able to recall them.
Now you're quite bitten, as you can't simply SET DELETED ON and let the sql engine care for deleted records. Indeed you have to introduce a deleted bit field in all tables in SQL Server (or other server databases not knowing a deletion flag)

How does this affect all your queries? Adding AND deleted=0 into the where clauses? For every table in the FROM list? As said this turns every outer into an inner join. You will have a hard time adding all OR NULL clauses to compensate this.

The simple solution is being straight forward with the strict rules: If you think about it deleted=0 is not a filter condition as you might think first. It's a condition for joining data you normally never need to think about, as you can only join existing records anyway, but indeed joining further tables ON table.deleted=0 is keeping out deleted records from joining and is keeping the left/right/outer/inner join type intact.

So our product query becomes simpler without misusing the WHERE clause in this way:

SELECT * FROM products prd LEFT JOIN origincountries oc ON oc.id = prd.origincountryid AND oc.deleted=0 WHERE prd.deleted=0

The only deleted=0 condition to put into the where clause is the condition on the main table you start selecting from. You can't put the prd.deleted=0 into the join condition, too. That would only prevent the join of origin countries to deleted products, but not remove deleted products from the result. That's the only thing to consider and a bit more complicated about it, the deleted=0 condition IS a filter condition for the main table of a query, but it's a join condition for any further joined table.

Since I have to do with such databases I am more rigid about such rules, as it helps preventing errors. The price it comes with is low and if it gets to performance optimizations all dirty tricks off that ideal route can be chosen, but don't do something in the prospect of it becoming machine optimized one day.

Let me think about how to solve that without direct join condition:

SELECT * FROM products prd, origincountries oc WHERE ((oc.id = prd.origincountryid AND oc.deleted=0) OR prd.origincountryid IS NULL) AND prd.deleted=0

Really? What about more joins, then?

In case of an inner join you can go with

SELECT * FROM products prd, origincountries oc WHERE oc.id = prd.origincountryid AND oc.deleted=0 AND prd.deleted=0

Yes, that's possible and again a bit shorter to write and might be optimized better by some sql engines, if it becomes more joins and the join order plays a role. But do you really want to think about every query you need to amend? If you find the recipe of making the extra deleted=0 checks in all join conditions you do so and don't have to think about the join types. You can even make it a full or at least half way automated process to change all queries of your application.

Keeping conditions apart from each other in their real meaning makes it easier to maintain and expand queries. That does not only apply to such a big impact of adding a deleted flag to all tables of a database in a real database server.

Bye, Olaf.

 

[Olaf Doschke]

Other scenarios, where this type of going through all your queries is needed: multi-tenency capability or affillates. Any data extension partitioning your data into data of some client, tenency or affiliate. Or as already described the two partitions of deleted and undeleted data.

Bye, Olaf.

 

[Scott24x7]

Thanks everyone.
Olaf, your "LEFT JOIN" first example was perfect. Then I realized I need the "raw" value too, so I just get the column again, and don't "Transform" it. Worked perfectly.
Thanks very much, and I will look into SQL tutorial again, as I agree, it would be really really useful as I grow the application.
Cheers

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."