SQLTeam.com | Weblogs | Forums

Another Complex TSQL Query

Hi experts,
The following is not very close to what is correct but hopefully you can see what I'm trying to do.

I want to select 1 row from Table1 where ColumnAttributes has a column named %DATE% but does NOT have a column named 'QuoteID'

And I need to return only 1 row per TableName.
Any ideas? Thanks

SELECT DBName, TableName, Rows, getDate()
FROM Table1 T
JOIN [DBA].[dbo].ColumnAttributes C on C.TableView = T.TableName

WHERE C.ColumnName Like ('%DATE%') AND Not Exists (Select * From DBA.dbo.ColumnAttributes C Where C.ColumnName NOT IN ('QuoteID');

and what defines the 1 row that should show up? just randomly?

1 Like
SELECT TOP (1) DBName, TableName, MAX(Rows) AS Rows, getDate()
FROM Table1 T
JOIN [DBA].[dbo].ColumnAttributes C on C.TableView = T.TableName
WHERE C.ColumnName LIKE ('%DATE%') OR C.ColumnName IN ('QuoteID')
GROUP BY DBName, TableName
HAVING MAX(CASE WHEN C.ColumnName Like ('%DATE%') THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN C.ColumnName IN ('QuoteID') THEN 1 ELSE 0 END) = 0
--ORDER BY NEWID()
2 Likes

All I need to return is a unique tableName. Thanks

What @ScottPletcher should do it
by the way how big is this Access application and any plans to migrate to a web app? react or angular? :smile:

Thanks guys. I removed the TOP (1) and it works perfectly. @yosiasz the client has no plans of moving away from Access. Don't understand "react or angular"

https://angular.io/

I would highly encourage your devs look into this. not that it will solve your issues for now but...

1 Like

Thanks. And to answer your question-- the Access app is 200 MB

@ScottPletcher
HAVING MAX(CASE WHEN C.ColumnName Like ('%DATE%') THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN C.ColumnName IN ('QuoteID') THEN 1 ELSE 0 END) = 0;

I have checked several tables and your code seems to be spot on. I don't understand how it EXCLUDES tables that have column QuoteID though, though it appears to do just that which is what I want.

but you said it should include those with QuoteID. never mind

I only need to return those tables having a column named %Date% AND do NOT have column named QuoteID. Again, the query Scott provided works perfectly, I just don't understand how.

:point_up_2: look at what he has after =

Yeah, the code's a bit obscure perhaps, but it's the most efficient way to do this.

It uses a HAVING statement. As we know, a WHERE clause tests on a single row; in contrast, a HAVING clause tests only the final result of a group of rows. The final tests are based on the columns in the GROUP BY in the query, in this case:
GROUP BY DBName, TableName

The code was:
"HAVING MAX(CASE WHEN C.ColumnName Like ('%DATE%') THEN 1 ELSE 0 END) = 1 AND"
"MAX(CASE WHEN C.ColumnName IN ('QuoteID') THEN 1 ELSE 0 END) = 0"

Each row gets evaluated using the CASE, but the final test of the value (= 1 / = 0) is not done until the entire group of rows has been processed.

So, let's walk thru some sample data rows:
DBName TableName ColumnName
db1 table1 date1
Will return a value of 1 for the first HAVING CASE (the LIKE is true).
Will return a value of 0 for the second HAVING CASE (the IN is false).

db1 table1 column2
Will return a value of 0 for the first HAVING CASE (the LIKE is not true). But, notice that the HAVING is doing a MAX of the value. Since a 1 has already been found, the value will remain at 1.
Will return a value of 0 for the second HAVING CASE (the IN is false). The value remains at 0.

Say there are no more rows for those db1, table1 values. The first value is 1, the second value is 0; both conditions are true and the group will be included in the results.

db1 table2 date1
same as above, 1 / 0
db1 table2 column2
same as above, 0 / 0 for the row, still 1 / 0 for the group
db1 table2 QuoteID
Will return 0 for the first HAVING but 1 for the second HAVING. Since the second HAVING is also a MAX, the group values are now:
1 / 1
db1 table2 someothercolumn
Will return 0 and 0 for the row values, but the max group values are still 1 / 1.
End of rows for ( db1, table2 ).
This group will NOT be selected because the second value is 1 and not 0, so the AND condition fails.

2 Likes