SQL Query: Simulating an "And" Over Several Rows Instead of Sub-Querying

SQL query: Simulating an AND over several rows instead of sub-querying


SELECT contentID
FROM tags
WHERE tagID in (334, 338, 342)
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = 3


--In general
SELECT contentID
FROM tags
WHERE tagID in (...) --taglist
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = ... --tagcount

SQL Query using IN but where all results have to have the same grouping ID


SELECT ProductSetID, COUNT(*) AS CountOfMatchingRows
From MyTable
WHERE AttributeId IN (14,17 )
GROUP BY ProductSetID
HAVING COUNT(*) = 2

How to select multiple records from a table with one field?

Query: (no self-join needed regardless of how many attributes are required; what is required is writing them in the where clause, and counting them for the having clause)

select warehouse_id
from wh_at
where attribute_id in (101, 201)
group by warehouse_id
having count(distinct attribute_id) = 2
;

With the sample data documented at the end, this produces the following output:

WAREHOUSE_ID
------------
W01
W04

The test data is shown below. I added a few more examples, to make sure the query works correctly for those cases. Especially the last example, where the same attribute is shown for the same warehouse more than once. (Perhaps this is not possible in your data due to a unique constraint, but if that is so, then you should mention it in your question.)

create table wh_at (id, warehouse_id, attribute_id) as
select 1, 'W01', 101 from dual union all
select 2, 'W01', 201 from dual union all
select 3, 'W02', 106 from dual union all
select 4, 'W02', 209 from dual union all
select 5, 'W03', 156 from dual union all
select 6, 'W03', 201 from dual union all
select 7, 'W04', 101 from dual union all
select 8, 'W04', 201 from dual union all
select 9, 'W04', 303 from dual union all
select 10, 'W05', 101 from dual union all
select 11, 'W05', 101 from dual
;

How to optimize SELECT statement with multiple sub-queries

You should be able to use the following:

SELECT a.ID,
a.repID,
a.artistID,
min(case when t.statusID = 4 then t.timestamp end) created,
min(case when t.statusID = 5 then t.timestamp end) claimed,
min(case when t.statusID = 6 then t.timestamp end) proof,
min(case when t.statusID = 8 then t.timestamp end) approved,
count(case when t.statusID = 6 then id end) proofcount
FROM Advertising AS a
LEFT JOIN Tracking t
on a.id = t.id
WHERE a.statusID = 8
GROUP BY a.ID, a.repID, a.artistID;

How Can I get multiple Sub queries from From Clause using JSQLparser ?

So here is a little code snipped. You have to know, that the first table/subselect is stored within the fromItem from your PlainSelect and the followings within the joins. You have to process both. Thats due to the parsing architecture JSqlParser uses.

Within JSqlParser you have two types of traversing the returned object hierarchy: 1. visitor pattern, 2. direct object hierarchy access. I implemented both within this little example.

EDIT: To find hierarchical all subqueries JSqlParser identifies, I included Type 3, which is kind of a hack, since I somehow missused the deparser.

public class SimpleSqlParser10 {
public static void main(String args[]) throws JSQLParserException {
String sql = "SELECT * FROM myTable, (select * from myTable2) as data1, (select b from myTable3) as data2";
Select select = (Select)CCJSqlParserUtil.parse(sql);
System.out.println(select.toString());


System.out.println("Type 1: Visitor processing");
select.getSelectBody().accept(new SelectVisitorAdapter(){
@Override
public void visit(PlainSelect plainSelect) {
plainSelect.getFromItem().accept(fromVisitor);
if (plainSelect.getJoins()!=null)
plainSelect.getJoins().forEach(join -> join.getRightItem().accept(fromVisitor));
}
});

System.out.println("Type 2: simple method calls");
processFromItem(((PlainSelect)select.getSelectBody()).getFromItem());
if (((PlainSelect)select.getSelectBody()).getJoins()!=null)
((PlainSelect)select.getSelectBody()).getJoins().forEach(join -> processFromItem(join.getRightItem()));


System.out.println("Type 3: hierarchically process all subselects");
select.getSelectBody().accept(new SelectDeParser() {
@Override
public void visit(SubSelect subSelect) {
System.out.println(" found subselect=" + subSelect.toString());
super.visit(subSelect); }
});
}

private final static FromItemVisitorAdapter fromVisitor = new FromItemVisitorAdapter() {
@Override
public void visit(SubSelect subSelect) {
System.out.println("subselect=" + subSelect);
}

@Override
public void visit(Table table) {
System.out.println("table=" + table);
}
} ;

private static void processFromItem(FromItem fromItem) {
System.out.println("fromItem=" + fromItem);
}
}

This one outputs

SELECT * FROM myTable, (SELECT * FROM myTable2) AS data1, (SELECT b FROM myTable3) AS data2
Type 1: Visitor processing
table=myTable
subselect=(SELECT * FROM myTable2) AS data1
subselect=(SELECT b FROM myTable3) AS data2
Type 2: simple method calls
fromItem=myTable
fromItem=(SELECT * FROM myTable2) AS data1
fromItem=(SELECT b FROM myTable3) AS data2
Type 3: hierarchically process all subselects
found subselect=(SELECT * FROM myTable2) AS data1
found subselect=(SELECT b FROM myTable3) AS data2

I can't run this query, but when i simulate it in phpmyadmin, it returns the rows

You can't reference your the table you are deleting from in a subquery of DELETE/UPDATE queries. Instead, use a LEFT JOIN and check where the joined table is NULL (meaning it had no rows that matched the ON condition).

Try to run delete queries within a transaction when testing it, that way you can rollback if the query deleted more rows than intended.

DELETE u
FROM sy_user AS u
LEFT JOIN tb_master_pegawai AS tp
ON tp.ID = u.user_id
WHERE tp.ID IS NULL

Transform sub-queries to joins

OP I think this is the query you are going for, this still requires 2 subqueries, but I don't believe your original query functioned as intended.

You could remove the loginTimes subquery, and use MAX(loginTime) in the outer SELECT list, but then you'd need to GROUP BY every field in the order table, which is arguably just as unclean.

The following query retrieves the UserId, latest LoginTime and the entire order record for the user's most recent order:

SELECT u.userId, 
u.userName,
l.loginTime,
o.*
FROM userProfiles u
INNER JOIN ( SELECT userId,
loginTime = MAX(time)
FROM loginTimes
GROUP BY userID) l ON u.userId = l.userId
INNER JOIN ( SELECT *,
rowNum = ROW_NUMBER() OVER (PARTITION BY userId
ORDER BY enterDate DESC)
FROM orders) o ON u.userId = o.userId AND o.rowNum = 1

Working on SQLFiddle

With a one-to-many relation, how do you get every row of table A if table B doesn't reference some rows of A?

Try using a left join, which will prevent removing any blogs which happen to have no tags.

SELECT
b.id,
COALESCE(GROUP_CONCAT(bt.tagid), 'no tags available') AS tagids
FROM blogs b
LEFT JOIN blogtags bt
ON b.id = bt.blogid
GROUP BY
b.id;

Note that I use COALESCE to display an appropriate message for those blogs which have no tags.

Check if a subset exists in SQL


SELECT DISTINCT Order_ID 
FROM Orders T1
WHERE NOT EXISTS (
SELECT Product_ID
FROM Orders
WHERE Order_ID = 2
EXCEPT
SELECT Product_ID
FROM Orders
WHERE Order_ID = T1.Order_ID);

Okay, that's probably not the most efficient way, but hey! The DBMS will have to look at every row anyway.



Related Topics



Leave a reply



Submit