|
As your joins are many to one (History to Person) you are going to need a number of queries and/or views to get all the different results.
I would create view(s) that joined all the tables, using inner joins where where the FK is required and left joins where the FK is optional and then search that view.
Alternatively use joins via the PersonID FK.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
select p.* ,e.*,s.*,jh.*
from persons p
inner join Education e on e.PersonID = p.PersonID
inner join Skills s on s.PersonID = p.PersonID
inner join JobHistory jh on jh.PersonID = p.PersonID
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This looks great but what about the conditions? For instance I want to find someone who meets the following conditions:
Education = Masters
Skills = Computer Networking
Experience = 6 years
Also I would like the search to match lower case spelling of Masters and Computer Networking, how are those 2 things I've just mentioned achieved. Thanks for your help.
|
|
|
|
|
Add a where clause with the conditions and user LOWER(Education) = 'masters'
You are screwed with the 6 Years, this should have been stored as a numeric and then you could use Duration > 6
You may also want to look into SOUNDEX filtering for the text fields
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select TblA.*,TblB.*,TblC.*,TblD.* from persons TblA
inner join Education TblB on TblB.PersonID = TblA.PersonID
inner join Skills TblC on TblC.PersonID = TblA.PersonID
inner join JobHistory TblD on TblD.PersonID = TblA.PersonID
|
|
|
|
|
Hi all,
I have a table called Singer and another called Song in Access. The Singer table has a primary key of SingerID and the Song table has a primary key of SongID and a foreign key of SingerID. I'm trying to add data to the Song table and I'm getting the error, "You cannot add or change a record because a related record is required in table 'Singer'. Why am I getting that error? Any help will be greatly appreciated, thanks in advance for your help.
|
|
|
|
|
A Singer must exist before a Song can be sung by the singer.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
modified 25-Aug-12 12:48pm.
|
|
|
|
|
Hi Eddy, thanks for replying.
So you are saying that I will get the error message I mentioned if I try to add a song into the Song table before I add an singer for that song in the Singer table?
|
|
|
|
|
Yes. The song you try to enter does not have a (valid) SingerId. Create a singer, like "Fat Lady", give her number 1 and enter your song with her SingerId. You'll find that the song will be accepted.
If you want to "point" to other tables from your Song-table, then the data in the other table has to exist before the Foreign Key is entered.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
C:\Projects\KCDXlogger>dbt kcdx "describe Artist ; describe Title ; describe Playlist
DBT V6.0 -- Simple SQL interface Sir John E. Boucher 2003
describe Artist
Name Type1 Type2 Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False
2 records affected.
describe Title
Name Type1 Type2 Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False
2 records affected.
describe Playlist
Name Type1 Type2 Nullable Unique Read only
-------- --------------- -------------------- -------- ------ ---------
CreTim System.DateTime DBTYPE_DATE True False False
Text System.String DBTYPE_WVARCHAR(255) True False False
PlaTim System.DateTime DBTYPE_DATE True False False
ArtistId System.Guid DBTYPE_GUID True False False
TitleId System.Guid DBTYPE_GUID True False False
5 records affected.
|
|
|
|
|
Thank you all for replying. I have solved this problem. It is as someone had said, you can't create a song before you have a singer.
|
|
|
|
|
Yes, which is very limiting, no?
|
|
|
|
|
PIEBALDconsult wrote: Yes, which is very limiting, no?
Yes, it is. But that was not his original question, it was about referential integrity. So his statement can be rewritten as
you can't create a song before you have singers.
|
|
|
|
|
Shameel wrote: you can't create a song before you have singers.
Don't be shocked too much, but I lied (just a little bit). Of course it's possible with the correct schema;
| Id | SongWriter |
|---|
| 1 | Paul McCartney | | 2 | Mick Jones |
| Song | WrittenBy |
|---|
| 1 | A world without love |
You can't have a singer sing a song before the song is written.
Creating a table-structure can be hard; a schema that has already been tried and used by a coworker, is a schema that has probably been tested by the co-workers user-base. Meaning, there's little chance that his code contains errors.
..and sometimes, you have to answer beyond the scope of the question. That must be what I enjoy most of the forums; reading strange questions and strange answers that make you go "hmz". Sometimes one can add a helpful answer, sometimes it's merely a waste of time. The most interesting discussions often start with "care to explain" or "why do you.."
Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
ok, I get you. I made a pretty bad assumptions that the singer is the writer. 
Eddy Vluggen wrote: Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.
I agree. I started learning many things when I started answering questions here. Helping others is a great way of learning.
|
|
|
|
|
Shameel wrote: the singer is the writer
I don't think the OP is concerned with the writer at all.
And if he wanted to track writers he would still be best served by a many-to-many schema.
|
|
|
|
|
Right, but the cause of the problem is a limiting schema. He wanted to create a song before he had singers, therefore the schema was the problem, not the code. 
A better schema allows one to add either first and then associate them later. And furthermore, his way requires you to add duplicates of a song that is performed by many singers -- which is bad.
|
|
|
|
|
Hi
string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
MySqlConnection con = new MySqlConnection(conn);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "Insert_Image";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?description", SqlDbType.VarChar).Value = txtdesc.Text;
cmd.Parameters.AddWithValue("?fullstr", SqlDbType.VarChar).Value = path;
con.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
Session.RemoveAll();
My code is giving error at executenonquery, "Input string not in correct format". The values in path and txt dec are correct.
Can you please tell where i am wrong?
Regards
Inderjeet Kaur
Inderjeet Kaur
Sr. Software Engg
|
|
|
|
|
The AddWithValue method expects the actual value to be passed rather than the data types, so you should write something like:
cmd.Parameters.AddWithValue("?description", txtdesc.Text);
|
|
|
|
|
Thanks alot!
I am new to MY SQL database and my problem is solved.
Inderjeet Kaur
Sr. Software Engg
|
|
|
|
|
You're welcome.
However, this problem isn't related to My SQL in any way, rather it is to do with ADO.NET.
|
|
|
|
|
When I use MySQL I use an at (@), not a question mark (?) -- I thought that was the problem, does it work with the question mark?
|
|
|
|
|
Dunno, but it works without any mark too. I only use the @-sign within the Sql-statement, the AddWithValue method always without.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
i am new to databse design. i want to design a schema for my bug tracking project . dont know how to start !!
plz help
Himanshu.
himanshu_1002@yahoo.com
Himanshu
|
|
|
|
|
This[^] may help you to start.
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
