Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Why this SQL statement is not working?

lizytalizyta Member Posts: 55
Is this the correct way to call for these 3 tables???

Call adoRec.Open("SELECT * FROM IMSImport, IMSMarket, IMSProducts", adoCon, adOpenKeyset, adLockReadOnly)

Comments

  • infidelinfidel Member Posts: 2,900
    : SELECT * FROM IMSImport, IMSMarket, IMSProducts

    It depends on what you're trying to do. My guess is this statement will return every row from each of these tables joined to every row in each of the other tables.

    For example,

    IMSImport contains:
    alpha
    beta
    gamma

    IMSMarket contains:
    foo
    bar

    IMSProducts contains:
    apple
    orange
    banana

    Your query as it stands would return something like this:
    alpha foo apple
    alpha foo orange
    alpha foo banana
    alpha bar apple
    alpha bar orange
    alpha bar banana
    beta foo apple
    beta foo orange
    beta foo banana
    beta bar apple
    beta bar orange
    beta bar banana
    gamma foo apple
    gamma foo orange
    gamma foo banana
    gamma bar apple
    gamma bar orange
    gamma bar banana

    See what I mean? You're basically just querying for every combination of rows that can be derived from those three tables.

    What you need is a WHERE clause that pulls out the rows you want from the candidates.

    i.e.

    SELECT *
    FROM IMSImport, IMSMarket, IMSProducts
    WHERE IMSImport.id = IMSMarket.import_id
    AND IMSMarket.id = IMSProducts.market_id

    ... of course, you will want to refer to the correct primary/foreign key relationships between your tables.
  • lizytalizyta Member Posts: 55

    THANKS A LOT

    ;-)
  • bednarjmbednarjm Member Posts: 82
    : Is this the correct way to call for these 3 tables???
    :
    : Call adoRec.Open("SELECT * FROM IMSImport, IMSMarket, IMSProducts", adoCon, adOpenKeyset, adLockReadOnly)
    :

    I would determine the fields you need instead of the select *

    As a general rule the select * is a bad coding practice. If you've got many columns on the rows (I've got one database with 80+ col's) then why bring back all the extra data - just bring the data needed. Your network admin and dba's will be very happy.

    Depending on the database platform the above statement would do one of three things:
    1) All data from all three tables
    2) Data that has some common field
    3) A cartesion product of all three - this would probally crash your server depending on the size of the tables.


    jim
  • bednarjmbednarjm Member Posts: 82
    : Is this the correct way to call for these 3 tables???
    :
    : Call adoRec.Open("SELECT * FROM IMSImport, IMSMarket, IMSProducts", adoCon, adOpenKeyset, adLockReadOnly)
    :

    I would determine the fields you need instead of the select *

    As a general rule the select * is a bad coding practice. If you've got many columns on the rows (I've got one database with 80+ col's) then why bring back all the extra data - just bring the data needed. Your network admin and dba's will be very happy.

    Depending on the database platform the above statement would do one of three things:
    1) All data from all three tables
    2) Data that has some common field
    3) A cartesion product of all three - this would probally crash your server depending on the size of the tables.


    jim
  • infidelinfidel Member Posts: 2,900
    : Depending on the database platform the above statement would do one of three things:
    : 1) All data from all three tables
    : 2) Data that has some common field
    : 3) A cartesion product of all three - this would probally crash your server depending on the size of the tables.

    I couldn't remember the term, "cartesian product". Thanks for the memory jog.
  • Andre YoungAndre Young USAMember Posts: 0

    ______ < http://forcoder.org /> free ebooks and video tutorials about || PL/SQL, Scratch, Java, Delphi, Visual Basic, Swift, Visual Basic .NET, Ruby, Perl, JavaScript, Python, Objective-C, PHP, R, MATLAB, Assembly, C#, C++, C, Go LabVIEW, Rust, Prolog, Scheme, ML, Crystal, Fortran, COBOL, Bash, ABAP, F#, SAS, VBScript, FoxPro, Logo, Alice, Awk, Julia, Scala, Transact-SQL, Ada, Apex, Lua, Clojure, Kotlin, Erlang, Hack, Lisp, D, Dart || _________

Sign In or Register to comment.