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
Generate SQL Insert Script from Excel Worksheet
Mysql: How to Copy Rows, But Change a Few Fields
How to Do Multiple Case When Conditions Using SQL Server 2008
Difference Between Right & Left Join VS Right & Left Outer Join in SQL
Postgresql Wildcard Like for Any of a List of Words
How Do SQL Exists Statements Work
Max Length for Client Ip Address
Postgresql - Fetch the Rows Which Have the Max Value for a Column in Each Group by Group
Disable All Table Constraints in Oracle
Sqlite: Current_Timestamp Is in Gmt, Not the Timezone of the MAChine
When Would I Use Xml Instead of SQL
Drop All Tables Whose Names Begin with a Certain String
Update Columns Values with Column of Another Table Based on Condition
How to Select Multiple Rows Filled with Constants
How to Select Date Without Time in SQL
SQL - Query to Get Server's Ip Address
How to Write Update SQL with Table Alias in SQL Server 2008
How to Delete a Fixed Number of Rows with Sorting in Postgresql