Is this SQL query possibile?

Discussion in 'OT Technology' started by SubOptimal, Mar 7, 2010.

  1. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    I need a SQL query that will find columns A and B WHERE X, then if that is found, start a new query of the table looking for columns A, C, and D where Y?

    SELECT A, C, D
    FROM table
    WHERE Y
    IF
    (SELECT A, B
    FROM table
    WHERE X)
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You would be better of giving sample data, and your desired result. But what you're really doing is running a query on a subset of data and that is doable using the IN statement.
     
  3. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    time, sender, receiver, type, code, weight
    1,12,50,80,11,2
    2,50,12,11,80,18
    3,12,50,80,11,16
    4,12,60,90,13,2
    5,60,12,13,90,18
    6,12,60,90,13,16
    7,12,70,55,66,77
    8,12,70,88,99,00


    SELECT * from table WHERE weight = 18
    THEN look for weight = 16 WHERE the sender and receiver are reversed and the type and code are also reversed
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That doesn't make any sense - there's no reason to search for weight 18, if you're then going to look for weight 16.

    Give us the output you want as well.
     
  5. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    Sorry for not being more clear... Take the first three shipments

    time, sender, receiver, type, code, weight
    1,12,50,80,11,2
    2,50,12,11,80,18
    3,12,50,80,11,16

    12 -> 50 weight of 2
    50 -> 12 weight of 18
    12 -> 50 weight of 16

    I want to see senders that received a package weighing 16 after sending one weighing 18.
     
  6. SIGirl

    SIGirl Super Duper Moderator Super Moderator

    Joined:
    Nov 1, 2001
    Messages:
    22,681
    Likes Received:
    1,381
    Location:
    Austin, TX
    Can't you just do a self join table as t1 and t2 then have t1.sender = t2.receiver and vice versa then do the weight?
     
    Narc likes this.
  7. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    I just started learning SQL maybe two weeks ago and googled self joins, but I don't think it would work because the type, code and weight are actually in another table. Sorry I didn't mention that earlier...
     
  8. RyeBread

    RyeBread If you tell the truth you don't have to remember a

    Joined:
    Mar 16, 2000
    Messages:
    11,036
    Likes Received:
    12
    Location:
    42.520686, -83.305014
    you can join more than one table, and there are of course different types of joins (left outer, etc)

    a quick table diagram would help get your query together faster - else, like a lot of hack's will try it, flesh out a quick database in access, perform the join/query via the query builder, then "view sql"

    :mamoru:
     
  9. SIGirl

    SIGirl Super Duper Moderator Super Moderator

    Joined:
    Nov 1, 2001
    Messages:
    22,681
    Likes Received:
    1,381
    Location:
    Austin, TX
    Oh I thought it was all in one table. Join the tables or do a subquery.
     
    Narc likes this.
  10. SIGirl

    SIGirl Super Duper Moderator Super Moderator

    Joined:
    Nov 1, 2001
    Messages:
    22,681
    Likes Received:
    1,381
    Location:
    Austin, TX
    This. It's kind of hard for us to tell you exactly what you need when we have no idea how your db is set up.
     
    Narc likes this.
  11. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    Yeah I've been able to join more than one table, but I don't see how you could do a self join to more than one table. Then again I'm a newb. :hs:


    table1 = time, sender, receiver
    1,12,50
    2,50,12
    3,12,50

    table2 = type, code, weight
    80,11,2
    11,80,18
    80,11,16

    So here is what the two tables look like when they are joined:

    Sender 12 -> Receiver 50 with Type 80 -> Code 11 with Weight of 2
    Sender 50 -> Receiver 12 with Type 11 -> Code 80 with weight of 18
    Sender 12 -> Receiver 50 with Type 80 -> Code 11 with weight of 16

    I need to find all weight's of 18, then look ahead where the Sender and Receiver is reversed, and the Type and Code is reversed, and the weight is 16.
     
  12. SIGirl

    SIGirl Super Duper Moderator Super Moderator

    Joined:
    Nov 1, 2001
    Messages:
    22,681
    Likes Received:
    1,381
    Location:
    Austin, TX
    I suggested the self-join when I thought you had all that in one table.
     
    Narc likes this.
  13. SIGirl

    SIGirl Super Duper Moderator Super Moderator

    Joined:
    Nov 1, 2001
    Messages:
    22,681
    Likes Received:
    1,381
    Location:
    Austin, TX
    Do they share anything? Do you have a foreign key in one referencing the other?
     
    Narc likes this.
  14. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    Yeah, sorry for not being more specific... Is there a way to do this with just SQL, or is programming where you can run a query, get the results then use that as variables for another query the only way?
     
  15. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    FUUUUUUUUUU, yeah they are related by an ID column....
     
  16. SIGirl

    SIGirl Super Duper Moderator Super Moderator

    Joined:
    Nov 1, 2001
    Messages:
    22,681
    Likes Received:
    1,381
    Location:
    Austin, TX
    You can execute a query in sql server management studio if you just want to see the result.
     
    Narc likes this.
  17. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    Nah, I don't have that. I'm using SQLite and I've been able to do everything so far with SQL, so I'd like to solve this problem with just SQL if possible. I'm kind of curious about just how much is possible with SQL too...
     
  18. RyeBread

    RyeBread If you tell the truth you don't have to remember a

    Joined:
    Mar 16, 2000
    Messages:
    11,036
    Likes Received:
    12
    Location:
    42.520686, -83.305014
    :werd: I was going to suggest something similar, run the query to a grid view, but it looks like SQLite which he just mentioned he is using is only commandline interface?

    looks like he's gonna be dumping/writing files to check the results.
     
  19. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    You can type a query in the CLI, or use a programing language that is capable of interacting with the database and put the results in variables. I'd like to figure out how to do this in just SQL if possible...
     
  20. RyeBread

    RyeBread If you tell the truth you don't have to remember a

    Joined:
    Mar 16, 2000
    Messages:
    11,036
    Likes Received:
    12
    Location:
    42.520686, -83.305014
    SQL itself can utilize variables - whether SQLite can or not I don't know - I also am not certain that it will/can allow those variables to be externally referenced.

    In an old job I did some of this when creating views, but as an example from a very quick google search:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111791
     
  21. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    I don't think SQLite supports variables with declare, I think you're supposed to use variables in the programming language you use to interact with the database... So I guess that's my only option if I want to accomplish this type of query?
     

Share This Page