SQL subqueries in C#

Question

I’m not a very experienced programmer so please bear with me! I have to extract data from a table (Chemie) based on data from another table (ProdDesigns). The obvious answer would be to use sub queries. Something like this:

SELECT chemie.rmcode, 
       chemie.sio2, 
       chemie.al2o3, 
       chemie.fe2o3, 
       chemie.feo, 
       chemie.tio2, 
       chemie.cao, 
       chemie.mgo, 
       chemie.na2o, 
       chemie.k2o, 
       chemie.cr2o3, 
       chemie.p2o5, 
       chemie.sic, 
       chemie.c, 
       chemie.mno, 
       chemie.zro2, 
       chemie.b2o3 
FROM   chemie 
       INNER JOIN proddesigns 
               ON chemie.rmcode = proddesigns.rmcode 
WHERE  chemie.rmcode = (SELECT proddesigns.rmcode 
                        FROM   proddesigns 
                        WHERE  proddesigns.batchno = 1234)

The problem is that the subset of data from table ProdDesigns will ALWAYS be multiple records, so I get the error “At most one record can be returned by this sub query”.So I’m thinking of using temporary tables and attempted the following:

conn.Open();
OleDbCommand cmdRM = null;
cmdRM = conn.CreateCommand();
cmdRM.CommandText = (@"SELECT * INTO ##tempTest FROM ProdDesigns WHERE batchNo = 1234");
cmdRM.ExecuteNonQuery();
dataAdapter2 = new OleDbDataAdapter(@"SELECT Chemie.RMCode, Chemie.SiO2, Chemie.Al2O3, Chemie.Fe2O3, Chemie.FeO, Chemie.TiO2, Chemie.CaO, Chemie.MgO, Chemie.Na2O, Chemie.K2O, Chemie.Cr2O3, Chemie.P2O5, Chemie.SiC, Chemie.C, Chemie.MnO, Chemie.ZrO2, Chemie.B2O3 
        FROM Chemie INNER JOIN ##tempTest ON Chemie.RMCode = ##tempTest.rmCode
        WHERE ((Chemie.RMCode)= ##tempTest.rmCode)", conn);
dataAdapter2.Fill(dataS2, "Chemie");
dtChem = dataS2.Tables["Chemie"];

Any feedback would be greatly appreciated!!

This is my first post too so I do apologize for poor formatting, indentation, etc. — BabyDoll
– BabyDoll, Commented Dec 11, 2013 at 12:11
Please don't apologize. We all passed at this way.. You can read FAQ and How to Ask as a start ;) — Soner Gönül
– Soner Gönül, Commented Dec 11, 2013 at 12:15

Raphaël Althaus · Accepted Answer · 2013-12-11 12:20:28Z

5

I think you could just do

Version1

FROM   chemie 
INNER JOIN proddesigns 
           ON chemie.rmcode = proddesigns.rmcode and proddesigns.batchno = 1234

Version2 (result won't be different with an INNER JOIN)

FROM   chemie 
    INNER JOIN proddesigns 
               ON chemie.rmcode = proddesigns.rmcode 
WHERE proddesigns.batchno = 1234

Version 3

SELECT c.rmcode, 
       --xxx
FROM   chemie c
where exists (select null from proddesigns p
              where c.rmcode = p.rmcode
              and p.batchno = 1234)

Which one should you choose ?

Well, that's your choice. I would take (in order of preference) 3, 1, 2, as you don't really "need a join".

You just wanna take values from chemie where something is related in proddesigns.

And the exists clause is the clearer for me. But that's just a point of view.

But if you need datas from proddesigns, then use a join.

By the way, the versions with the JOIN could retrieve more rows than what you need. That shouldn't happen with your datas, but would happen if you had two proddesigns with same rmcode and batchno=1234.

edited Dec 11, 2013 at 12:20

answered Dec 11, 2013 at 12:08

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nunners Over a year ago

Didn't know you could JOIN on more than one condition. Learn something new everyday :)

Raphaël Althaus Over a year ago

@Nunners Well it's not a need with an INNER JOIN (you can put this in the where clause). It's often more usefull with LEFT JOIN.

DRapp Over a year ago

@BabyDoll, I would suggest the option with the JOIN vs EXISTS.. Historically, I've found that IN, NOT IN, EXISTS to a subquery is generally SLOWER than a JOIN based on a qualifying index -- (rmcode, batchno)

johnny · Accepted Answer · 2013-12-11 12:11:13Z

0

no need to use the subquery, append and proddesigns.batchno = 1234 after ON chemie.rmcode = proddesigns.rmcode and good to go

answered Dec 11, 2013 at 12:11

johnny

2,1222 gold badges28 silver badges46 bronze badges

Comments

DonBaton · Accepted Answer · 2013-12-11 12:13:34Z

0

Change

WHERE  chemie.rmcode = (SELECT proddesigns.rmcode 
                    FROM   proddesigns 
                    WHERE  proddesigns.batchno = 1234)

to

WHERE  chemie.rmcode in (SELECT proddesigns.rmcode 
                    FROM   proddesigns 
                    WHERE  proddesigns.batchno = 1234)

answered Dec 11, 2013 at 12:13

DonBaton

437 bronze badges

Comments

Alberto Solano · Accepted Answer · 2013-12-11 12:17:35Z

0

You can optimize your query changing:

WHERE  chemie.rmcode = (SELECT proddesigns.rmcode 
                        FROM   proddesigns 
                        WHERE  proddesigns.batchno = 1234)

to

 WHERE  proddesigns.batchno = 1234

I think you'll notice soon the difference in performance, without using a subquery.

An alternative would be to put this condition in the INNER JOIN statement:

INNER JOIN proddesigns ON chemie.rmcode = proddesigns.rmcode and proddesigns.batchno = 1234

answered Dec 11, 2013 at 12:17

Alberto Solano

8,2653 gold badges40 silver badges62 bronze badges

Comments

Thorsten Dittmar · Accepted Answer · 2013-12-11 12:23:42Z

0

You could simply add a TOP 1 to your subquery

WHERE  chemie.rmcode = (SELECT TOP 1 proddesigns.rmcode 
                        FROM   proddesigns 
                        WHERE  proddesigns.batchno = 1234)

This makes sure only one record is returned.

I know you got the solution already, but if you wanted one result for each result of the subquery, you could also have used IN:

WHERE  chemie.rmcode IN (SELECT proddesigns.rmcode 
                        FROM   proddesigns 
                        WHERE  proddesigns.batchno = 1234)

answered Dec 11, 2013 at 12:23

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

3 Comments

Raphaël Althaus Over a year ago

a little bit overcomplicated to use a subquery, no ?

BabyDoll Over a year ago

Thing is I do need more than one record returned, but I got the solution. Thanks for your feedback, I'm sure this tip will come in handy in future!

Thorsten Dittmar Over a year ago

@RaphaëlAlthaus Of course, but my solution fixes the error and he had the subquery already. :-)

Collectives™ on Stack Overflow

SQL subqueries in C#

5 Answers 5

3 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related