-3

How to execute this in one query?

@Input = Select id from table2 where title="hh"  
Output = `Select * from table1 where id=@Input

TABELE1

ID B1 B2 B3
1 ALPHA QWER OK
2 BRAVO ASDF OK
3 CHARLIE ZXCV OK

TABLE2

ID C1 C2
1 BRAVO ONETWO
2 BRAVO TRENETWUR
3 CHARLIE FIVESIX
4 ALPHA SEVENEIGHT 
Select * from TABLE1 where B1= (Select C1 from TABLE2 where concat(C2) like '%NETW%')

This is exactly what I am trying to do. But the query above doesn't still work. I prefer rows in TABLE1 as a result.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
BagZ
  • 11
  • 3
  • 1
    ``Select * from table1 where id IN (Select id from table2 where title="hh")`` – Akina Jun 10 '21 at 09:30
  • 1
    This causes an error to me and i don't know why – BagZ Jun 10 '21 at 09:33
  • `SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.title='hh'` – MatSnow Jun 10 '21 at 09:33
  • *This causes an error to me and i don't know why* Do you want us to predict the error message? – Akina Jun 10 '21 at 09:34
  • @MatSnow What is the reason for to use LEFT? – Akina Jun 10 '21 at 09:35
  • @Akina Good question. It's a habit of mine to use a `LEFT JOIN` by default. But in this case one can use just `JOIN` or `INNER JOIN` (functionally equivalent). But either way a join is to prefer over a subquery. https://stackoverflow.com/questions/2577174/join-vs-sub-query – MatSnow Jun 10 '21 at 09:42
  • @MatSnow INNER JOIN by default is more suitable, and LEFT only when it is needed really. If this does not influence the result then LEFT causes the server to perform excess and unneeded work. – Akina Jun 10 '21 at 09:45
  • @Akina Yes, I agree... a subquery can cause the server also to perform excess and unneeded work ;-) – MatSnow Jun 10 '21 at 09:47
  • @MatSnow I think that both queries (if remove LEFT from your query) will produce the same execution plan, so the work will be absolutely the same. – Akina Jun 10 '21 at 10:29
  • DO NOT some irrelevant tags. Tags are not a why for you to trick people into answering your question. They exist specifically so that we know whether a question is relevant to us, so only apply relevant tags. Using irrelevant tags is straight-up lying to people you're asking to volunteer their time to help a stranger. Do better. – jmcilhinney Jun 10 '21 at 10:41
  • #1242 - Subquery returns more than 1 row – BagZ Jun 10 '21 at 12:29

1 Answers1

0

You can use following snippet:

Output = Select * from table1 where id= (Select id from table2 where title="hh");

Sujit Sharma
  • 110
  • 10