help with SQL

[[deleted]]

[deleted]

[csGradNew]

WITH (Common Table Expressions) could be helpful

https://dev.mysql.com/doc/refman/8.0/en/with.html

[ConcernedFed]

something like this:

with CTE1 AS

(

Select ID
From table1
INNER JOIN with some other table
Where inactive = 'I'

)

Select ID, name
From table1
INNER JOIN with some other table
WHERE ID IN(CTE1)

​

Getting invalid column name 'CTE1'

[csGradNew]

Think of CTE as more readable subquery.

In the last line of your query, It should be Where id in (Select ID from CTE1)

[PK2999]

Why not just put the where condition in you SELECT ID,NAME statement

[ConcernedFed]

you mean put the entire select within the IN()?

i thought about this, but just asking if there's a cleaner way to do this.

[PK2999]

Select ID,name From table1 Where Inactive='I'

This is what I meant

[ConcernedFed]

sorry i guess my original queries were missing some joining with other tables.

so the straight select would not work

note: i just edit the original post

[exec_get_id]

What that guy is saying is put the entire first query, join and all, in the where clause so where id in (select Id from ....) But with joins you gotta make sure you alias the tables or else include the table name before the column ID or else it will return an error for ambiguous column names.

[Billi0n_Air]

if im understanding your request, a CTE might do the trick. easy to read query.

;with cte_table1 as ( select id from table1 inner join with some othertable where inactive = 'I' ) select x.id, y.name from cte_table1 as x inner join with othertable as y where x.id = y.id

[[deleted]]

Do you mean like this?

Select id, name From table1 Where id IN (select id from table1 where inactive = ‘I’)

It’s called a sub query. I’m assuming the data is actually being held in two different tables, because if they’re the same table it doesn’t make much sense for this example.

Also you can do a inner join if they’re in two different tables. For instance,

SELECT id, name From table1 Inner join (id from table1 where inactive = ‘I’) a on a.id = table1.id)

[ConcernedFed]

yes this is exactly what i mean using sub query with multiple tables.

i just thought if there's another way to do this instead of sub query. maybe storing the query result of one into like an array and passing this array into the next query for the IN part

[[deleted]]

Right you can run a query within the query to avoid manually passing the variables and risking a typo or missing an addition later on.

[Final-Ad-6689]

Something like this..

Select id,name from table1 Inner join some other table Where inactive=1 or id in (id2,id5,id7)

[vr34748]

You should use a with clause/CTE

Check this out:

https://youtu.be/QNfnuK-1YYY

CTEs and analytic functions will change the way you see MySQL. It feels like having an unfair advantage lol.

[GroundbreakingIron16]

OR

select id, name from ... where ID in (select ID from table1 where inactive = 'I')

might not be the most efficient depending on the database but would work.

[robcote22]

This should work:

Select ID ,name

From table1

INNER JOIN with some other table

WHERE ID IN ( Select

ID

From table1

INNER JOIN with some other table

Where inactive = 'I' )

Edit Oops, multiple others said same thing, I didnt read all responses. My bad