SubQueries in Select statments

[vanhalb]

I have a complex query that invloves selecting a value from another query in a format like:

SELECT t_object_1.Name, (SELECT t_objectproperties.Value FROM t_objectproperties WHERE (((t_objectproperties.Object_ID)=[t_object_1].[object_id]) AND ((t_objectproperties.Property)="Latency"))) AS Latency
FROM t_object AS t_object_1 INNER JOIN t_object ON t_object_1.ParentID = t_object.Object_ID
WHERE (((t_object.Object_Type)="object") AND ((t_object.Stereotype)="RdcApplication") AND ((t_object_1.Stereotype)="HFQueuingPort"));

This works fine in Access, but when I try the same code in VB.Net, it does not like the subquery in the Select statement. Is there a syntax difference with VB.Net?

(For those who might think that this is an odd way to put together a query when I could just use a join, I've simplified the query above, it actually contains about twenty such references in Access, many of which contain null values.)

 

[cjard]

This works fine in Access, but when I try the same code in VB.Net, it does not like the subquery in the Select statement. Is there a syntax difference with VB.Net?

No, in the sense that VB will pass your query to the database for evaluation, but..
Yes, in the sense that the sql command builder reads the query and tries to guess what columns are present and how they are updated

(For those who might think that this is an odd way to put together a query when I could just use a join, I've simplified the query above, it actually contains about twenty such references in Access, many of which contain null values.)

It is an odd way and yes, you could and should jsut use a join as it will perform much better. in 10 years of writing some of the most complex SQLs you could hope to see, i have never, ever performed a subquery in the select list. Clean your SQL up and VB wont have a problem with it..

 

[vanhalb]

OR joins

I would gladly use joins, but I have either or conditions, and I'm not sure how to create an or join.

To clarify, I have a Field_ID in a table, let's call it Table 1, that connects to another table, let's call it Table 2, in two possible fields. It will always be either Field A, Field B, or neither. It will never be Field A and Field B.

If I create a join from Table 1, Field_ID to Field A and Field B in Table 2, I only get the both/and condition. What I really need is an either/or with the possible of null.

Do you know how I might create this condition without creating several queries?

 

[cjard]

For best performance, one table must be the "driving table"

The driving table contains records you want to see. The driven table might or might not contain records relating:

Colours.Colour, Fruits.Fruit
Red, Apple
Yellow, Lemon
Silver, Null
Blue, Blueberry
Orange, Orange
Gold, Null

Colour is the driving table, fruits are driven. There are no fruits that are naturally silver or gold

SELECT
  *
FROM
  table1 t1
  LEFT OUTER JOIN
  table2 t2
  ON
    t1.FieldA = t2.FieldA [B]OR[/B]
    t1.FieldA = t2.FieldB

See.. it wasnt so hard
A join isnt rocket science, and the condition is evaluated for every row and only those that pass, are returned