none
OLEDB Connection, Need Query Syntax to Include Nulls (maybe?) RRS feed

  • Question

  • This may seem a bit strange.  I've got to merge the data on a few separate database files.  They're inventory across a few separate locations, and each has a slightly different list.   I've compiled a single list of all items from all the databases (allelec in following code).  Total there should be over 19k records, only 12k appear because if the item doesn't appear in ALL of the files, it ignores it.  When I tried using "OR" instead of "AND", it went for epic fail just by volume of records.  Anyone know how to fix this?  Thanks in advance!  (ps, if this is the wrong forum and you feel I should know, please point me to the right one rather than just telling me it's wrong)

                oCmd.CommandText = @"SELECT allelec.item, file01.onhand, file02.onhand AS Expr1, file03.onhand AS Expr2, file04.onhand AS Expr3, file09.onhand AS Expr4 FROM allelec, file01, file02, file03, file04, file09 WHERE (allelec.item = file01.item) AND (allelec.item = file02.item) AND (allelec.item = file03.item) AND (allelec.item = file04.item) AND (allelec.item = file09.item)";

              

     


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    • Moved by Bob Wu-MTModerator Thursday, February 23, 2012 6:49 AM ado.net issue (From:Windows Forms Data Controls and Databinding)
    Wednesday, February 22, 2012 3:28 PM

Answers

  • Would probably help if we knew what type of database you are working with since SQL syntax can be different. In any event, the below SQL is for Microsoft Access and it implements a LEFT JOIN to get data from several tables based upon the ID column in Table A:

    SELECT [Table A].ID, [Table B].ID, [Table C].ID, [Table D].ID
    FROM (([Table A] LEFT JOIN [Table B] ON [Table A].ID = [Table B].ID) LEFT JOIN [Table C] ON [Table A].ID = [Table C].ID) LEFT JOIN [Table D] ON [Table A].ID = [Table D].ID


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by psifreak Friday, February 24, 2012 8:43 PM
    Thursday, February 23, 2012 3:01 PM

All replies

  • Hi psifreak,
    I think this issue is related to ADO.NET, I will move it to ADO.NET forum for better support.
    Sorry for any inconvenience this may cause.
    Best Regards


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 23, 2012 6:48 AM
    Moderator
  • Would probably help if we knew what type of database you are working with since SQL syntax can be different. In any event, the below SQL is for Microsoft Access and it implements a LEFT JOIN to get data from several tables based upon the ID column in Table A:

    SELECT [Table A].ID, [Table B].ID, [Table C].ID, [Table D].ID
    FROM (([Table A] LEFT JOIN [Table B] ON [Table A].ID = [Table B].ID) LEFT JOIN [Table C] ON [Table A].ID = [Table C].ID) LEFT JOIN [Table D] ON [Table A].ID = [Table D].ID


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by psifreak Friday, February 24, 2012 8:43 PM
    Thursday, February 23, 2012 3:01 PM
  • It's old dbase 4 .dbf files.  Using the visual foxpro drivers, for oledb.  Forgot that part, sorry.  I'll give that code a try, thanks! 

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Friday, February 24, 2012 5:47 PM
  • Forgot this part too, I'm aiming for a C# in code query, but I'm not locked on that.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Friday, February 24, 2012 6:00 PM
  • It doesn't matter which language you use, it's the database and driver that matters. If the syntax doesn't work for you then you may want to try the Visual FoxPro forum. At a minimum they should be able to help with the SQL statement.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 24, 2012 6:14 PM
  • Got it working!  Thanks!

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Friday, February 24, 2012 8:43 PM