Clash Royale CLAN TAG

generating conditional combinations in sql

I have the following table:

╔═══╦══════════════╦═════════════╗
║ ║id ║name ║
╠═══╬══════════════╬═════════════╣
║ ║ 1 ║a1 ║
║ ║ 1 ║b1 ║
║ ║ 2 ║b2 ║
║ ║ 3 ║c1 ║
║ ║ 2 ║c2 ║
║ ║ 4 ║a2 ║
╚═══╩══════════════╩═════════════╝


I have the below query which does the following:

For input (a,b,c) it returns all possible combinations of the form (aX,bX,cX) where X is anything present after "a/b/c" in the records.

(a,b) generates (a1,b1) , (a1,b2) according to my table.

Running this query

select t1.id as t1_id, t1.name as t1_name,
t2.id as t2_id, t2.name as t2_name,
t3.id as t3_id, t3.name as t3_name,
from (select * from table where name like 'a%') as t1
cross join (select * from table where name like 'b%') as t2
cross join (select * from table where name like 'c%') as t3


I want to modify the query such that it only returns me the rows where there are no similar ids.

For example 1st row has t1_id = 1, t2_id = 1 so there are two similar ids. This shouldn't be in the result.

4 Answers
4

select * from (select t1.id as t1_id, t1.name as t1_name,
t2.id as t2_id, t2.name as t2_name,
t3.id as t3_id, t3.name as t3_name,
from (select * from table where name like 'a%') as t1
cross join (select * from table where name like 'b%') as t2
cross join (select * from table where name like 'c%') as t3) as ft where ft.t1_id<>ft.t2_id and ft.t1_id<>ft.t3_id and ft.t2_id<>ft.t3_id


Filter out the unwanted rows after cross join.

where t1.id<>t2.id and t1.id<>t3.id and t2.id<>t3.id


Probably doesn't really needs sub-queries.

SELECT
t1.id as t1_id, t1.name as t1_name,
t2.id as t2_id, t2.name as t2_name,
t3.id as t3_id, t3.name as t3_name
FROM table AS t1
JOIN table AS t2 ON t2.name LIKE 'b%'
JOIN table AS t3 ON t3.name LIKE 'c%'
WHERE t1.name LIKE 'a%'
AND t1.id <> t2.id
AND t1.id <> t3.id
AND t2.id <> t3.id


Simplify your query by removing the subqueries. Then use the where clause for filtering:

where

select t1.id as t1_id, t1.name as t1_name,
t2.id as t2_id, t2.name as t2_name,
t3.id as t3_id, t3.name as t3_name,
from table t1 cross join
table t2 cross join
tablet3
where t1.name like 'a%' and
t2.name like 'b%' and
t3.name like 'c%' and
t2.id not in (t1.id) and
t3.id not in (t1.id, t2.id);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.