alexdresko wrote:
Paul.Lewis wrote:
alexdresko wrote:
Obviously that's not a real SQL query, but it illustrates what I want to do.
I want to get all rows from a table where each row's child collection contains at least one row.
Is that possible with LLBLGen?
Wouldn't this query return all rows from A that contain rows in B?
SELECT A.*
FROM A
INNER JOIN B ON A.ID = B.FKID
Why yes it would! I think I'm just appplying my predicate expressions to the wrong object. I'll let you know if I can't figure it ou.
Actually, it would repeat row A for each joined row in B even though it doesn't show any columns from B.
In order to get single instances of A.* rows, you need to use the DISTINCT keyword or the IN clause
SELECT DISTINCT A.*
FROM A
INNER JOIN B ON A.ID = B.FKID
Here is real world example
use Northwind
select A.* from Customers A
inner join Orders B on A.CustomerID = B.CustomerID
-- Returns 830 rows
select distinct A.* from Customers A
inner join Orders B on A.CustomerID = B.CustomerID
-- Returns 89 rows
select A.* from Customers A
where A.CustomerID in (select CustomerID from Orders)
-- Returns 89 rows
