Database

26-Aug-07 22:44

HI I am having problems with the join i need to do to get the required result. This is the problem: I have two tables one with user data and one with user profile image. I need to join these tables to get each user with his profile image (user might have many profile pictures but only one is set to be default=1). But some users might not have profile images uploaded yet. So the problem wen i do left join i get an entry with users having no profile images and also i get TWO entries for the same users that have a profile image : one with the right answer and one with an empty image. But i want my join to select empty image ONLY wen the user has no image uploaded. Any help??

/\|-||\/|/\|)

Colin Angus Mackay27-Aug-07 0:55

27-Aug-07 0:55

I don't understand how unless you actually have a placeholder row for an empty image. If that is the case then it should be simple to add something into the WHERE clause to filter that out.

You might want to post the relevent parts of the datamodel and the query you are using. It might make it easier to visualise your actual situation.

-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits

My website

Halawlaws27-Aug-07 1:41

27-Aug-07 1:41

SELECT C.UserID
FROM Contact AS C
LEFT JOIN Album AS A ON A.UserID = C.UserID AND A.Type = 1
LEFT JOIN AlbumImage AS AI ON AI.AlbumID = A.AlbumID AND AI.'Default' = 1

Contact:
UserID pk
...

one to many

Album:
AlbumID pk
UserID fk
Type
...

one to many

AlbumImage:
ImageID pk
AlbumID fk
Default

a contact may have many albums one of which is for profile pictures and all the profile pictures can be selected from album image

if a user did not upload a picture yet he will have an album for profile picture (of type=1) but without album images i want to select this user so i can give him throught the code later a default picture of my choice

/\|-||\/|/\|)

Colin Angus Mackay27-Aug-07 2:27

27-Aug-07 2:27

I think you need to add a WHERE clause

WHERE AI.AlbumID IS NULL

Also, if Album will always exist at least once with a Type of 1 for every contact then that should be changed to an INNER JOIN. The join to AlbumImage stays as a LEFT JOIN.

Halawlaws27-Aug-07 4:05

27-Aug-07 4:05

I try this wat i get is trully all the results that i need except the users that has a profile picture ill get the result that is without a profile picture. So this way ill get all users without a profile picture eventhough they have one. But wat i need is to get the profile picture for the people who has one and get null for people who don't

thx for ur suggestion though

/\|-||\/|/\|)

Michael Potter27-Aug-07 5:31

Michael Potter

27-Aug-07 5:31

I don't understand the difficulty you are having. You clearly specify the qualifications for the join in your question. Did you not integrate Default into the JOIN? A sample of your code would be helpful if the query below does not solve your issue.

SELECT 
   u.UserId,
   u.UserName,
   u.OtherUserStuff,
   p.UserImage
FROM
   [UserTable] u
LEFT JOIN
   [ProfileTable] p
   ON ((u.UserId = p.UserId) AND (p.Default = 1))

Halawlaws27-Aug-07 5:56

27-Aug-07 5:56

I did integrate default into the join the problem is simple:
If a user has more than one profile picture but only one is selected as default and if i do the join as u and i suggested ill get two results one witht the profile picture and another one without the profile picture. It is this second result that i wish to eliminate
thanks anyway for your help Smile | :)

/\|-||\/|/\|)

Michael Potter27-Aug-07 6:01

Michael Potter

27-Aug-07 6:01

You have lost me. Given the join I specified (and I think it mirrors your attempt) the only way you should get more than one row per user is if there are Duplicate User Records or muliple Default=1 per user.

Halawlaws27-Aug-07 20:05

27-Aug-07 20:05

By definition the Left join gives two results. I have read something similar to the following in many references about the left join:
"If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table."
I want that this is y i chose the left join but the thing is that apparently the left join gave an extra record for all users having a profile picture with all columns set to null for the right table but i need this to happen ONLY wen there is no profile picture. This is the question how can I make this diffrenciation??

Ps: There is no multiple users Records nor there is multiple default = 1 per user
Btw wen I add the default = 1 in the where clause i get strict result about only users with profile pictures and users without are neglected...

/\|-||\/|/\|)

reference data from database to textboxes

Michael Potter28-Aug-07 3:24

Michael Potter

28-Aug-07 3:24

You can't put the default=1 in the WHERE clause. It has to be put in the JOIN clause as I listed in a previous post. The WHERE clause will eliminate non-matching rows (a null in the default column does not match 1). I have long since stopped qualifying my joins in the WHERE clause because of this type of issue.

The sample query I listed before should get you your desired results if your tables are structured as you have stated. If you are getting duplicate results, there is something in your structure that I don't understand. I do LEFT JOINS all the time without the side effect you are getting.

Can you post the actual query you are using?

RaveRare26-Aug-07 22:29

RaveRare

26-Aug-07 22:29

dear everyone

according to your insturctions i have successfully filled a listbox with student names on my form.

then i wished that when i click a student name in the list, my defined data-adapter should bring certain record from database and fill my textboxes, checkboxes, comboboxes with values relating to that particular student.

i am wonder to see that when i did that, my dataset is having same row again and again so i am finding error message when i am binding textbox source to the table.

help me plz

Re: reference data from database to textboxes

Colin Angus Mackay27-Aug-07 0:56

Oh No, How this happened????

27-Aug-07 0:56

Be patient, you asked the exact same thing[^] just two hours previously.

RaveRare26-Aug-07 20:21

RaveRare

26-Aug-07 20:21

Database Login

Bonsta26-Aug-07 8:47

Bonsta

26-Aug-07 8:47

Hi got this code that is a potential solution to my Database login problem.It was originally in VB but I ran a converter on it. Now I have a OleDbDataReader method called Item(String) that was there when the code was in VB but now does not exist on the OleDbDataReader class in C#(dont know if that possible). Here is the full code. I need help to find the equivalent of this Item method in C#. The code connects to a access database and compares a textbox password entry to one in the database under the same user name given on another textbox...Also if there are any changes that I need to make to connect to an SQL Database Server.

private void btnLogin_Click(object sender, System.EventArgs e)
{
//The connection string is used to describe the type of database, the security information and the location to the database.
string ConString = "Provider=Microsoft.Jet.OLEDB.4.0assword=\"\";User ID=Admin;Data Source=\"databasename.mdb\";";
//Create a new connection object and assign the ConString Connection String above
OleDbConnection DBCon = new OleDbConnection(ConString);
// g_login is a global variable defined in a Module that captures the login name and passes it from form to form. To create this, just create a Module, say Module1.vb and in it put "Public g_login as String" {g meaning global and login to represent the global login}
GlobalVariables.g_login = this.textBox1.Text;

string strPassword = this.textBox2.Text;

if (GlobalVariables.g_login == "" || strPassword == "")
{
MessageBox.Show("You are missing information. Please make sure that both the username and password fields are filled out.", "Missing Info");
this.textBox1.Focus();
return;
}
// The database has two fields in the Users table. A UserID field, which is the primary key and declared as a text. The other field is Password, which is a text as well.
string strsql = "SELECT [UserID], [Password] FROM Users WHERE [UserID]='" + GlobalVariables.g_login + "' ";

OleDbCommand cm = new OleDbCommand(strsql, DBCon);
OleDbDataReader dr;
bool valid = false;
bool HasRows = false;
try {
DBCon.Open();
dr = cm.ExecuteReader();
if (dr.HasRows) {
while (dr.Read()) {
if (strPassword == dr.Item("Password")) {
valid = true;
}
}
HasRows = true;
}
dr.Close();
}
catch (OleDbException exO) {
MessageBox.Show(exO.Message);
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
finally {
if (DBCon.State == ConnectionState.Open) {
DBCon.Close();
}
cm = null;
dr = null;
DBCon.Dispose();
GC.Collect();
}
iCount = iCount + 1;
if (valid == true) {
Form n = new Form4();
n.Show();
this.Hide();
}
else if (iCount == 3) {
MessageBox.Show("Contact Developers!", "Invalid Info");
this.Close();
}
else if (HasRows == false) {
MessageBox.Show("Invalid user name, try again!", "Invalid Info");
this.textBox1.Focus();
this.textBox1.Text = "";
this.textBox2.Text = "";
}
else {
MessageBox.Show("Invalid password, try again!", "Invalid Info");
this.textBox2.Focus();
this.textBox2.Text = "";
}

}

Thanks in Advance.

Is this chair taken

Re: Database Login

Marek Grzenkowicz26-Aug-07 21:43

Marek Grzenkowicz

26-Aug-07 21:43

Shouldn't you use square brackets instead of round brackets?

dr.Item("Password") => dr.Item["Password"]

If you used this converter[^], I think that you experienced this:

Known Issues
The converter cannot currently determine whether SomeName(SomeParam) is a method call, or a reference to an indexer - this will not be possible without actually compiling the code or performing some much deeper analysis of the code.

Why the Column from Type BigInt must have name [Size] in SQL 2000 to work well ?!!

I Believe In GOD26-Aug-07 5:39

I Believe In GOD

26-Aug-07 5:39

Dear Developer ,
Have a good day first

I have a little problem in understanding this situation

I created table in SQL 2000

contains two Column 1 - FileName varchar (50)
2 - Size bigint (8)

and I am using C# ADO.NET to insert values in this table with Parameters
and every thing goes well ......

My Question Now , Why the Column Name Must Be "Size" to accept insert Values as BigInt ( in other word when I change the column Name "Size" to any "fooo"
it does not work ) I got message cann't convert nvarchar to bigint

thanx all for your help

P.S
try to see Master.Sysfiles Design Table there is a column named size

I know nothing , I know nothing

Re: Why the Column from Type BigInt must have name [Size] in SQL 2000 to work well ?!!

Michael Sync26-Aug-07 17:44

Michael Sync

26-Aug-07 17:44

I don't think there is a problem like that...

Could you please show us the code that you are trying to insert the values and the type and name of the table from database?

Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read it here. Thank you.

Pleaes help me with this query

Meysam Mahfouzi26-Aug-07 4:22

Meysam Mahfouzi

26-Aug-07 4:22

I've got a table like this:

title	|	value	|	keyId
-----------------------------
aaa	|	10	|   	1
aaa   	|   	11  	|   	2
bbb   	|  	10 	|	1
ccc   	| 	11 	| 	2

How can I retrieve "title"s which meet this condition: (keyId=1 and value=10) and (keyId=2 and value=11)

The desired output for above query should be 'aaa'.

Please notice that the following query:

SELECT * FROM table WHERE (keyId=1 AND value=10) AND (keyId=2 AND value=11)

returns nothing, and the following query:

SELECT * FROM table WHERE (keyId=1 AND value=10) OR (keyId=2 AND value=11)

returns all rows while only the first two records are desired.

Please help me!

Re: Pleaes help me with this query

Colin Angus Mackay26-Aug-07 5:35