Anothe tricky SQL problem - any takers?

[mystomachehurts]

Consider a table thus:

id  type tag c1 c2 c3 etc
1 1 1 NULL
2 2 1 aaa
3 3 1 NULL
4 1 2 NULL
5 2 2 bbb
6 3 2 NULL
7 1 3 NULL
8 2 3 aaa
9 3 3 NULL

• Each row has a unique id that I want to look up.
• Additonally rows are 'linked' according to their tag... this is also unique.

Simply enough, if I want all the type 3 rows, I run

select * from tbl where type = 3;

Is it possible to build an SQL statment that would only return rows
of type=3 where there is a row with the same tag that has type=2 AND c1='aaa'

in this case it would return row id 3 and row id 9,

If it was the run for:
type=3 having a matching type=2 AND c1='bbb' it would only return row id 6.

If it was the run for:
type=3 having a matching type=2 AND c1='xyz' it would return an empty set.

I've played with self joins but I just can't seem to get it to work.

Many thanks for any help!

[alanruss]

What database is this on? If you use a procedure you can use the CASE / IF statements (on PL/SQL - Orcale) or T-SQL for MS SQL / Sybase.

[john_p]

select * from thing where id=any(select id from thing where type=2 and c1='bbb') and type<>2

That works in MySQL, I think classic SQL would be "where id in (select id from ..)"

Edited by john_p on Thursday 20th December 15:10

[BlueEyedBoy]

I think this is what you are trying to say.

select * from table1 t1 where type = 3 and id in (select id from table1 t2 where t1.tag = t2.tag and t2.tag = 2 and c1 = 'aaa')

[mystomachehurts]

Many thanks everybody, some good stuff to try out there and test the performance against.

What makes it trickier, and I didn't want to complicate the initial question, is that this SQL statement is actually generated inside a PHP script which accepts some human readable paramters, these paramters get translated into the Where clause of the select.

So I'll see which ones work that allow me the simplest options of performing the translation.

[cyberface]

mystomachehurts said:

this SQL statement is actually generated inside a PHP script which accepts some human readable paramters, these paramters get translated into the Where clause of the select

SQL injection exploit anyone?

[mystomachehurts]

cyberface said:

mystomachehurts said:

this SQL statement is actually generated inside a PHP script which accepts some human readable paramters, these paramters get translated into the Where clause of the select

SQL injection exploit anyone?

Ooooooo you think you are so smart....

I'd like to see you try!

In fact I'm not sure why I'm even bothering to answer this post

• I always terminate the generated SQL with a ';' So likely any SQL insert is not going to compile properly. And the SQL is only generated after I've parsed what you sent me.

• If anything you send me cannot be resolved into field and column name pointers by looking up from rows that already exist in other lookup tables I barf and tell you it's invalid. (I promise you that there are no SQL commands in the lookup tables)

Next you'd have to be a mind reader to understand the paramters that are passed into the php code that enables this process to even start, let alone work.

Finally, you'd need to login via HTTPS to even get the PHP Session ID that will allow the script to start.

Kiss my face....

[cyberface]

You're not seriously challenging me to hack your servers Ade old bean are you?

Hell, at least you've thought about it, some sites just pass unchecked, unescaped strings straight into SQL statements run by a user with full privs...

[tigger1]

mystomachehurts said:

Consider a table thus:

id type tag c1 c2 c3 etc



Is it possible to build an SQL statement that would only return rows
of type=3 where there is a row with the same tag that has type=2 AND c1='aaa'

It's possible to build any SQL statement you want
(MS-SQL is my chosen flavour - and I like to self-join, sometimes...)

If we call your table "thus" then

select * 
   from thus t1 with (nolock)
       inner join thus t2 with (nolock)
           on t1.tag = t2.tag
              where t1.type = 3
              and t2.type = 2
              and t2.c1 like 'aaa' --you're not clear if you mean
                                   --t2.c1 or t1.c1

This will return all lines with matching tags, where one has type = 3, and the other has type = 2 AND c1 = aaa.


Obviously you can then subst variables into this instead of the 3,2 and 'aaa', and then create some neat php.

HTH.

[mystomachehurts]

tigger1 said:

This will return all lines with matching tags, where one has type = 3, and the other has type = 2 AND c1 = aaa.

I only want to return the row with type=3 providing there is a row with the same tag that has type = 2 AND c1 = aaa

Do you see the difference? I don't want the row that has c1=aaa, I only want the row with type = 3 IF there is a row type 2 AND with c1 = aaa that has the same tag number.


cheers
Ade

[tigger1]

mystomachehurts said:

I only want to return the row with type=3 providing there is a row with the same tag that has type = 2 AND c1 = aaa

Do you see the difference? I don't want the row that has c1=aaa, I only want the row with type = 3 IF there is a row type 2 AND with c1 = aaa that has the same tag number.

In that case, yes, then

select t1.* 
      from thus t1
             inner join thus t2 
                  on t1.tag = t2.tag
                  and t2.type = 2
                  and t2.c1 like 'aaa'
                  and t1.type = 3

will return anything with type 3, where there exists something with a matching tag number, and c1 = 'aaa' - BUT it does not return the row with type 3 / c1 = 'aaa'.

Actually the code I've just written above is EXACTLY the same as what I did before, but I've swapped an and for a where...which TBH I'm not sure makes a difference without trying it (it's too early, and we've got no coffee!!! FFS)

Because I'm only selecting t1.* from "t1", and I've limited that to rows with t.type=3, the join is effectively just removing rows from t1. Should work.