Database

7-Nov-02 11:33

I'm a newbie with SQL so please take it easy on me... Blush | :O

Anyways...

I have 2 tables

clients ( email, name, phone, address, agencyid )
agency ( agencyid, email, name, phone, address, website )

I need to pull all the agency records out and determine if they have clients/members...???

How would I do this...i'm so lost???

Currently I have something like:

SELECT agency, clients WHERE agency.agencyid = clients.agencyid AND this is where i'm lost

Is there anyway inside the SQL statement I can determine if an agency has no members...? This is what I need returned...agencies with no members...???

Do I have to use a for loop?
I'm using mySQL and PHP BTW

$query = "SELECT * agency";
$result = mysql_query($query); // Store all agencies inside result set
$row = mysql_fetch_array($result);

// Iterate all agencies and determine if they have members
for($i=0; $i<mysql_num_rows($result); $i++){
  $clientResult = mysql_query("SELECT * clients WHERE clients.agencyid = $row[$i].agencyid");
  if(mysql_num_rows($clientResult) == 0)
    // Store this record for display
}

Do I have to do it like the above or can I accomplish this from ONE SQL statement...? Or Am I doing it all wrong...?

Thanx! Smile | :)

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Paul Watson7-Nov-02 11:47

7-Nov-02 11:47

Hockey wrote:
Is there anyway inside the SQL statement I can determine if an agency has no members...? This is what I need returned...agencies with no members...???

The in statement in SQL is brilliant. Basically in your case you need to do this: select * from agency where Not AgencyID in (select AgencyID from Clients)

That should return all agency rows which do not occur in clients.

in can be used many ways. For instance if you have a pre-defined list of values to check for you can do: select * from agency where Not AgencyID in (1, 2, 3, 4)

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

alex.barylski7-Nov-02 12:58

7-Nov-02 12:58

I've actually used the in statement before, but only like in your second example...

Anyways, I appreciate the help Paul...I was getting pretty desperate...

One question? I just read a little more on the subject and it appears I could accomplish something very similar using a left join...???

I will have to read up on this appraoch more, but which would be more efficient...?

Thanx again

Cheers! Smile | :)

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

alex.barylski7-Nov-02 13:10

7-Nov-02 13:10

Hey Paul, i'm getting the following error, you wouldn't care to help me figure out why...? Smile | :)

<br />
SQL-query :  <br />
<br />
SELECT * FROM agency WHERE NOT agencyid IN (SELECT agencyid FROM clients) LIMIT 0, 30<br />
<br />
MySQL said: <br />
<br />
<br />
You have an error in your SQL syntax near 'SELECT agencyid FROM clients) LIMIT 0, 30' at line 1<br />

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Paul Watson7-Nov-02 21:08

7-Nov-02 21:08

Hockey wrote:
MySQL

Doh, I just assumed you meant Microsoft SQL Server. I have about 2 hours experience with MySQL and most of it was spent cursing Big Grin | :-D

There are some differences between the TSQL that MS SQL Server uses and the SQL that MySQL uses.

*the following is pure guessing so I could be utterly wrong*

If I had to guess, the error seems to be saying that a in statement has a maximum of 30 returnable rows. There might be a setting in MySQL for this.

If none of that works then in answer to your other question about rather using a join..

SELECT
    Clients.ClientID, 
    Clients.AgencyID AS ClientAgencyID, 
    Agency.AgencyID, 
    Agency.Name AS AgencyName
FROM
    Clients FULL OUTER JOIN
    Agency ON Clients.AgencyID = Agency.AgencyID
WHERE    (Clients.ClientID IS NULL)

Sorry but I can't test it against a MySQL box so I do not know if it works on MySQL, works on MSSQL though.

As for performance I am no DBA so I really could not tell you which is faster.

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

markkuk8-Nov-02 2:08

markkuk

8-Nov-02 2:08

MySQL doesn't support subSELECTs at all. You must replace the nested query, see the MySQL manual.

alex.barylski8-Nov-02 8:11

Saving a recordset in an XML stream

8-Nov-02 8:11

Brutal...

I ended up using multiple queries inside a for loop...proabbly not the MOST elegant solution, but it works...

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

SELECT ... FOR XML AUTO

Alexandru Savescu6-Nov-02 23:42

Alexandru Savescu

6-Nov-02 23:42

Hello,

I want to use the SELECT some recordsets in an XML format (using the SQL Server 2000 features). I am using Carlos Antollini classes that wrap the ADO database clases. I am declaring a CADORecordset object, open it and use GetFieldValue (0, var), where var is a _variant_t object.

Its type is VT_ARRAY | VT_UI1 (this means an array of bytes, rights?). The parray member looks like this:

cDims = 0x0001; // one dimension array
fFeatures = 0x0080;
cbElements = 0x00000001;
cLocks = 0x0;
pvData = 0xsmth;

Dumping my array of bytes into a file looks awful. However, looking at the code the array looks like an array of BSTRs, but is not the XML encoded data I am expecting.

Can anyone help?
Thanks.

Best regards,
Alexandru Savescu

Best regards,
Alexandru Savescu

Alexandru Savescu5-Nov-02 23:33

Alexandru Savescu

5-Nov-02 23:33

I am saving an ADO recordset as an XML. It works fine. However I do not want to save into a file, I would like to save it into a stream, a CString, ostream etc. Anyone have an idea?

Thanks!

Best regards,
Alexandru Savescu

Re: Saving a recordset in an XML stream

Richard Deeming6-Nov-02 2:54

Richard Deeming

6-Nov-02 2:54

You can pass a Stream object to the Save method of the recordset. For example, in IIS 5 you can pass in the Response object, and the XML will be sent straight to the browser.

Datasets

Scoop5-Nov-02 23:15

Scoop

5-Nov-02 23:15

I am trying to retrieve data using datasets in C#. However when i use the select command "SELECT * from Some_table". if there is a field which has a null in it, when the dataset returns the XML, the field is not present. When I put data in this column, the field is then present in the returned XML.

Is there a setting for enabling null fields in the results.

Here is the code

<code>SqlCommand sqlCommand = new SqlCommand("Select * from MyTable",sqlConnection1);

SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = sqlCommand;
sqlConnection1.Open();
DataSet custDS = new DataSet();
custDA.Fill(custDS,strName);
sqlConnection1.Close();
strRet = custDS.GetXml();</code>

Alexandru Savescu5-Nov-02 23:37

Alexandru Savescu

5-Nov-02 23:37

May I ask why it bothers you this? The XML file is smaller. Smile | :)

Best regards,
Alexandru Savescu

Paul Watson6-Nov-02 3:15

6-Nov-02 3:15

This might help[^], search for "drops column fields with null values" to get to the relevant paragraph.

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

Scoop6-Nov-02 3:23

Scoop

6-Nov-02 3:23

Thanks;)

Paul Watson6-Nov-02 3:26