|
I think the problem you are having is the cross join on relationships. There should be no reason why you would get Jane Jones as both Wife and Son. Can you post the tables? For example, give me two or three lines of data for each of the three tables, adn I can look more closely at the query you have written.
Jeremy
Jeremy Oldham
|
|
|
|
|
Sounds good. I appreciate your help.
Here's a select on the top level CustomerID (which is '101' in this case)for the TA_Customer table (I used a reduced set of columns for brevity on this particular tables. The ones below it are, however, complete):
TA_Customer
------------
Customer_ID CustomerID LastName FirstName
155603 101 Jones Bob
155611 101 Jones Jane
155612 101 Jones Willy
TA_CustomerRelationships
-------------------------
CustomerID Customer_ID1 Customer_ID2 RelationshipID
101 155603 155611 2
101 155603 155612 3
TA_CustomerRelationshipsLU
---------------------------
RelationshipID Description
1 Husband
2 Wife
3 Son
4 Daughter
5 Father
6 Mother
7 Grandfather
8 Grandmother
9 Uncle
10 Aunt
11 Nephew
12 Niece
13 Cousin
For your convenience, here is the original query:
SELECT TA_Customer.Customer_ID,
TA_Customer.FirstName,
TA_Customer.LastCompanyName,
TA_CustomerRelationshipsLU.Description
FROM TA_CustomerRelationships INNER JOIN TA_CustomerRelationshipsLU ON
TA_CustomerRelationships.RelationshipID =
TA_CustomerRelationshipsLU.RelationshipID CROSS JOIN
TA_Customer
WHERE (TA_Customer.CustomerID = '101') AND (TA_CustomerRelationships.CustomerID = '101') AND
(TA_Customer.Customer_ID <> 155603)
Notice I added in an extra 'AND' clause. I only want to return the subordinate records. The top level record is the one for "Bob" whose Customer_ID is 155603.
As a reminder, I want the output to be this:
155611 Jane Jones Wife
155612 Willy Jones Son
Thanks for your help.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Try the following and let me know how it worked.
<br />
Select TA_Customer.Customer_ID, TA_Customer.FirstName, <br />
TA_Customer.LastName, TA_CustomerRelationshipsLU.[Description]<br />
FROM TA_Customer<br />
INNER JOIN (<br />
--Gives you the Relationships for the individual<br />
Select Customer_ID2, RelationshipID<br />
from TA_CustomerRelationships<br />
WHERE Customer_ID1 = 155603<br />
AND CustomerID = 101<br />
) b <br />
ON TA_Customer.Customer_ID = b.Customer_ID2<br />
INNER JOIN TA_CustomerRelationshipsLU<br />
ON b.RelationshipID = TA_CustomerRelationshipsLU.RelationshipID<br />
Jeremy Oldham
|
|
|
|
|
Sheesh! Just when I think I'm starting to get better at SQL, somebody shows me a query like this. I have no idea what exactly is going on in this query, but it worked like a charm. Thank you so much for your help. I really appreciate it.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Well let me give you a quick description.
First, I wanted to get only the customer numbers we needed that are related to the customer 155603 and is in the correct Customer Group 101. I did this be creating the following query:
--Gives you the Relationships for the individual<br />
Select Customer_ID2, RelationshipID<br />
from TA_CustomerRelationships<br />
WHERE Customer_ID1 = 155603<br />
AND CustomerID = 101
This query returns all the customers related to the Customer ID 155603 and within his group, as well as their relationship id.
Now all I needed to do is find out who those customers were (by their customer id) and what those relationships were (by relationID).
I did that by joining the TA_Customer table with the subquery which gives me the related customers information, and then joining the TA_CustomerRelationshipsLU table with the subquery which gives me the relation names.
I hope this helps explain the query a little bit.
Glad I could be of help!
Jeremy Oldham
|
|
|
|
|
Howdy all,
I'm using C# and ADO.NET to access an SQL Server Database, using SqlDataAdapter, DataSet et al.
What I want to do is create a new row, submit it to the database, and then retrieve the Identity field that SQL Server automagically generates. At the moment I have something like this:
string comm_str = String.Format( "SELECT Name, Image, IllustrationID FROM Illustrations");
SqlCommand comm = new SqlCommand( comm_str, m_sqlConn );
SqlDataAdapter da = new SqlDataAdapter( comm );
da.InsertCommand = da.SelectCommand.Connection.CreateCommand();
da.InsertCommand.CommandText =
@"INSERT INTO Illustrations (Name, Image) values(@Name, @Image)";
da.InsertCommand.Parameters.Add( "@Name", SqlDbType.Char, 0, "Name" );
da.InsertCommand.Parameters.Add( "@Image", SqlDbType.Image, 0, "Image" );
DataSet ds = new DataSet();
da.Fill( ds, "Illustrations" );
DataTable dt = ds.Tables["Illustrations"];
DataRow newRow = dt.NewRow();
newRow["Name"] = name.Length;
newRow["Image"] = imageBuffer;
dt.Rows.Add( newRow );
da.Update( ds, "Illustrations" );
ds.AcceptChanges();
newImageID = (int)newRow["IllustrationID"];
Now I think this code may look a little naieve, as I've cobbled it together from various books, but it does the job, apart from retrieving the IllustrationID field from the new record.
IllustrationID is set as an Identity field in the DB, so when a new record is inserted a value is generated by the DB. I'm guessing I need to update my DataSet, but I'm not sure how. Also, after updating my DataSet do I need to get a new DataTable, etc. or is this all handled automagically by ADO.NET.
Hope that makes some kind of sense and that someone has some advice for me!
TIA,
Pete
|
|
|
|
|
if ur using sql server u can use @@identity to get the last inserted record's id
"traffic lights are for people who can't make their own decisions" biz stuff about me
|
|
|
|
|
I'm really looking for a solution using the C# DataAdapter and DataSet classes.
Thanks anyway
|
|
|
|
|
If you use a proper DataSet definition (Project->Add New Item, select DataSet, rename it, OK, drag-drop an element "Illustrations", add all your fields as sub-elements of that) then you can define one of those fields as AutoIncrement. Then it does all the work for you.
Paul
Pleasently caving in, I come undone - Queens of the Stone Age, No One Knows
|
|
|
|
|
Would that work with multiple users?
Say the last inserted record had an ID of 34, and user A gets his DataSet and inserts the record. meanwhile user B has inserted a record. if User A then checked the ID field would it be 35 or 36?
does that make sense?
|
|
|
|
|
In case anyone else needs help with this, there is a good article
HTH,
Pete
|
|
|
|
|
Is it possible to use ADO.NET from unmanaged c++?
Cheers,
Clint
|
|
|
|
|
Hi all, Got a bit of a problem here, I'm using C#/ASP.NET and am attempting to connect to a SQL source, wich is located on a server(SILICON) I have used the below connection syntax:
String strConnection = "server=SILICON; database=PositonTracker01; integrated security=true;";
SqlConnection objConnection = new SqlConnection(strConnection);
String strSQL = "SELECT FirstName, LastName, Country " + "FROM Analyst";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
when i run this I get no data, just my html layout minus the data it should show, any suggestions?
|
|
|
|
|
- You've created a
SqlConnection object, but never opened it; - You've created a
SqlCommand object, but never executed it; - You haven't added any code to output the results of the query;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>
<title>Authorised Users
TEST
<asp:datagrid id="dgNameList"
="" runat="server" gridlines="None" backcolor="LightBlue" cellpadding="5" cellspacing="5" borderwidth="2" bordercolor="Black" <="" body="">
private void Page_Load(object sender, System.EventArgs e)
{
String strConnection = "server=SILICON; database=PositonTracker01; integrated security=true;";
SqlConnection objConnection = new SqlConnection(strConnection);
String strSQL = "SELECT FirstName, LastName, Country " + "FROM Analyst";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
dgNameList.DataSource = objCommand.ExecuteReader();
dgNameList.DataBind();
objConnection.Close();
}
|
|
|
|
|
|
I'm creating a login system and I am wondering about the design i have chosen because it seems to be counter productive. Basically I have 2 tables as it stands:
BTW - MEID is members id, AUTHID is auth_users id
1) auth_users
2) members
- PKID
- AUTHID
- EMAIL
- FNAME
- LNAME
- NLOGINS
- OPTIONS
- LASTLOGIN
- EXPIRES
Anyways, initially the login required me querying only the auth_users table, but that changed when features were added. Like account expiration, number of logins, last login time and date, account privileges, etc. So my question is this, should I sticj with the current design and basically run two queries at login (login permission is cached in a cookie, so once you've logged in the system won't bother you again, nor will it run the query to authenticate users)?
Should I maybe add the required fields to the auth_users table instead of keeping them in the members table so I only have to run one query at login or should I keep it as is so I have to run 2 queries at login?
Thanks
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
May I suggest a 3rd option?
Use 2 tables. Combine the auth_users and members table. Add a new table called user_props. user_props will need 3 columns.
pkid (ref auth_users)
prop_nm (char 50?)
prop_vl (text)
In the first table, plan on just keeping the absolute minimum # of fields you will need for about any system. (PKID, USERID, PSWRD, ACTV) may be enough.
Define your own prop_nm values and place all other user related details here, stored vertically. (Ideall you will have a 3rd table which has at least 2 columns and defines the prop_nm values you create.)
You will only ever need 2 queries and these should be encapsulated into a single ASP module, C# class or whatever. You will be able to add new properties whenever you want (there will be more, count on it.)
I use this tecnique for user definition and it has payed off many, many times. I don't recommend using this tecnique for just any data but for user login info, it is great.
Just my 2 cents.
|
|
|
|
|
Could I not store all account information inside a members table (access privileges, account expires, etc..) and only username and password inside auth_users. I'm thinking if I could then use an index on auth_users MemberID to quickly select the member information, this would be quite the optimization would it not?
i'm a total newbie with mySQL, so please forgive me if my terms are off.
]Thanks
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
Sure you could do it this way. The easiest way if you want a 1:1 relationship between auth_users and members is to either use the same PK or add a column to one or both tables mapping to the other tables PK. The downside to using the additional column in only one table is that you will not be able to do a reverse lookup from the other table. The downside to using an extra column in both tables is that you will have to keep their values in-sync.
If this is the method you are looking at, I would recommend that you set it up so that their PK fields are the same. This way there is only 1 field to keep in sync and since it is the PK of the table you won't ever have to worry about it.
The method that I recommended was intended to make the user-solution easier to adapt to new requirements (account expiration, lockouts, limit to IP address, require SSL, log all activity, admin privilidges, etc). My experience has been that user authentication and user management is always a moving target. As a system becomes more complex, accessible or just management changes its mind, there tends to be more and more requirement for access control, authentication and logging mechanisms. It is virtually impossible to predict those requirements in the beginning.
The solution I recommend coped with this by proving a flexible property assignment mechanism that would make the DB schema and related code relatively imune to requirement changes. You still have to deal with the code that actually implements user-rights, logging, expiration, etc.
|
|
|
|
|
Matt Gullett wrote:
The easiest way if you want a 1:1 relationship between auth_users and members is to either use the same PK
I didn't even think of that...thats a great idea...because there will always be that one to one relationship. The solution I was looking for was more optimization than anything, not ease of implementation. Using the above one to one relationship model, is it possible to query only the authentication table ('auth_users') initially using something like:
SELECT * FROM auth_user WHERE user='Test' AND pass='Test'
Ideally I would like to use the PKID of the resultset returned from the above query and somehow use another query to return the associated ('members') table record. It's to my understanding that my default SQL(mySQL) automatically uses indexes on primary keys??? So really whats happeneing when I call the above select statement the record is being being indexed, rather then being located sequentially? I don't know if i'm accurate in thinking this or not.
I wouldn't care so much if the ('auth_users') table was sequentially searched becuz it's small in size. However the members table will be quite large so to have a query sequentially search each record here would be quite redundant. I could just make one query if I stored all this data in one table. Basically I would like to know if the following:
SELECT * FROM auth_users WHERE user='Test' and pass='Test'
and then
pkid = auth_id;
SELECT * FROM members WHERE pkid = auth_id
would cause the second select statement to use indexing by default so to avoid sequentially iterating records in the members table again. This to me seemed like the optimized approach, seeing how auth_users is a small table, so using it to index the equivilent record in the members table made sense. However i'm rather new at mySQL/SQL so i'm not sure.
I was hoping you could shed some light on this subject for me?
I appreciate all the help so far.
Cheers!
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
Hockey wrote:
is it possible to query only the authentication table ('auth_users') initially using something like:
YES.
Hockey wrote:
Ideally I would like to use the PKID of the resultset returned from the above query and somehow use another query to return the associated ('members') table record.
You could do it this way, but typically you would just want to use a single SQL statement with a JOIN to get both the data from the auth_users and members table all in one statement.
SELECT A.PKID, A.USER, A.PASS, A.WHATEVERELSE, B.WHATEVER FROM auth_user A, members B WHERE A.user='Test' AND A.pass='Test' AND B.PKID = A.PKID
I don't know much about MySQL but every DB I have every worked with supports at least limited JOINS.
As far as the indexes go, every DB I have ever worked with maintains an index on PK fields. Leveraging the indexes a DB uses is the best way to insure performance. (Understanding how indexes work, what to index, etc is also pretty important, but for our discussion, this is enough.)
|
|
|
|
|
I just want to make sure I understand this correctly then (I really appreciate your help BTW).
With the code you provided above, the implicit JOIN, does the query use an index for both tables? I wanna make sure I don't sequentially iterate each record (especially on the members tables) if not nessecary. That is of course assuming my understanding of indexes is correct, but I think it's basically like a pointer.
So long as the above query uses an index on atleast the member table i'm happy.
Thanks
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|
I can't say that I'm 100% sure how MySQL will deal with this, but every DB I know of can handle at least 1 index per table involved in an SQL statement (with certain rules). Assuming MySQL at least provides for this (many DB's support many more indexes per table in SQL statment), YES it will use the indexes.
I know it sounds like I'm hedging my bet here, but the answer to your question should be YES unless MySQL really, really sucks. (From what I do know about MySQL it is designed for performance and this kind of optimization would be pretty important to performance.)
I have written an optimizing SQL parser/analyzer and I can say from experience that the most likely thing to happen is that the parser/analyzer will see that your SQL statement is using a PK (unique index) as part of the where clause (B.PKID = A.PKID) and will be smart enough to know to use th index. I'd be really surprised it it did not.
|
|
|
|
|
All i needed to hear. Atleast now I know the question would probably mysql specific and will ask if mysql supports multiple indexes in a single statement.
Thanks again!
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
|