I get the idea of the JOIN where different data from two tables is joined by a SQL statement using one common data item on a relational basis. I have some trouble using that from time to time, and often need more than one try to get the SQL statement right, but... I'm getting better.
This one is escaping me.
I have two tables in one database. Both have columns: id (int, autoincrement), Name (varchar), lref (int), iref (int) and Date (datetime). They have a bunch of other columns as well, ones which do not match each other.
I need to get a recordset of all records from both tables which occurred in a given month for a given client, containing those four columns and sorted by date. Using two separate recordsets is problematic due to the date sort. I thought the "id" field might be causing a problem, as it is unique, but leaving it out of the select does not help, and I am going to need it.
SELECT id,Name,Date,lref FROM Staff,Precord WHERE MONTH(Date)=12 AND YEAR(Date)=2007 AND iref=31 ORDER BY Date
Do I need to identify the tablename for each field? and How would the work in the WHERE clause? (YEAR(Staff.Date)=2007 OR YEAR(Precord.Date)=2007) AND .... Yikes.
[SOLVED] Getting data from two tables, but doesn't seem to
Moderator: General Moderators
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
[SOLVED] Getting data from two tables, but doesn't seem to
Last edited by Bill H on Sat Dec 29, 2007 3:45 pm, edited 1 time in total.
What do these two tables represent? Are they "related" in the sense that they are different entities that have a common property/field, like CustomerMaster and Orders, or are they similar entities that just happen to be stored in two different tables? The fact that they seem to share multiple fieldnames causes me to ask.
What field(s) would you use to join these tables? Obviously not the ID field, which is autonumber in both tables. Is it the Name field? If so, is that field unique in at least one of those tables?
It looks to me, at this point, as if you don't want a join at all, you want a union. Perhaps something like this (but I really don't understand what you are trying to do):
[Edit: BTW, using a fieldname like "Date", which is a reserved word, is a very bad practice.]
What field(s) would you use to join these tables? Obviously not the ID field, which is autonumber in both tables. Is it the Name field? If so, is that field unique in at least one of those tables?
It looks to me, at this point, as if you don't want a join at all, you want a union. Perhaps something like this (but I really don't understand what you are trying to do):
Code: Select all
SELECT id,Name,Date,lref FROM Staff
WHERE MONTH(Date)=12 AND YEAR(Date)=2007 AND iref=31
UNION ALL SELECT id,Name,Date,lref FROM Precord
WHERE MONTH(Date)=12 AND YEAR(Date)=2007 AND iref=31
ORDER BY Date- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Aha, I'll have to research the UNION. This appears to be a valuable lead. Thank you.
I've been doing fairly simple database for some time and am now starting to get into increasing complexity to more thoroughly use the richness of database technology.
They are two different kinds of transactions for clients, in-person interviews and telephone ones. Some data is common, such as location and a date, but much different data is gotten in the two interviews. I would have made the common data one table and the differing data in separate tables, but the phone one was added at a much later time and it wasn't known that it would be coming along when the first one was created.
Date, however, is actually not a reserved word in MySQL. It is a keyword, but is not reserved. From the MySQL docs,
I've been doing fairly simple database for some time and am now starting to get into increasing complexity to more thoroughly use the richness of database technology.
They are two different kinds of transactions for clients, in-person interviews and telephone ones. Some data is common, such as location and a date, but much different data is gotten in the two interviews. I would have made the common data one table and the differing data in separate tables, but the phone one was added at a much later time and it wasn't known that it would be coming along when the first one was created.
Date, however, is actually not a reserved word in MySQL. It is a keyword, but is not reserved. From the MySQL docs,
The word "Date" is simply too convenient not to use it, since MySQL permits.MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
I stand corrected. You will note that the exception is "because many people previously used them," which might be an indication that it's not considered good practice, though. It is a reserved word in some other languages or database engines, so it's preferred that you avoid using such names for greater portability. I would also argue that using "Date" is really not convenient, because it gives no hint as to what date is stored. Much better to use "EnrollDate", "InterviewDate", "PaymentDate", etc. 
OK, then you recognize the issue about entities. Your inclination to put the clients' data in one table and some of the supporting fields in related tables was sound. Sometimes it's just not practical to change something that already exists.
I hope you're taking care of San Diego, my old home town! I spent the first 30 years of my life there. I'll be visiting again in September, for my old Navy ship reunion.
OK, then you recognize the issue about entities. Your inclination to put the clients' data in one table and some of the supporting fields in related tables was sound. Sometimes it's just not practical to change something that already exists.
I hope you're taking care of San Diego, my old home town! I spent the first 30 years of my life there. I'll be visiting again in September, for my old Navy ship reunion.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
DevNetwork is always the right place to come. I suggested in my op that it did not seem to be a JOIN, and I was right. I didn't know what it was, and I got a prompt and accurate answer that taught me something new. The answer solved my problem very nicely. Thank you.
Yes, I have learned that generally speaking the less in one table the better. Well, not really, but specificity is good and not making use of the relational nature of a db is dumb.
Your point about specificity of column names has merit, but this table in particluar only has one date, the date of the event, so I fail to see the inconvience of it.
I think San Diego is taking better care of me than I am of it, but... This is a great place. I was here in 1963 when my ship was at Pt. Loma Submarine Base. I reported aboard and about 3 months later we left for a WestPac tour. When we came back it was to Mare Island for new batteries as part of a Guppy I refit. I returned in 1998 after a 2-year commuter relationship with the lady who is now my wife: could not get her to move to AZ, so I moved here. No regrets, for a lot of reasons.
Sept, as I'm sure you know, is a great time of year to visit. What ship?
Yes, I have learned that generally speaking the less in one table the better. Well, not really, but specificity is good and not making use of the relational nature of a db is dumb.
Your point about specificity of column names has merit, but this table in particluar only has one date, the date of the event, so I fail to see the inconvience of it.
I think San Diego is taking better care of me than I am of it, but... This is a great place. I was here in 1963 when my ship was at Pt. Loma Submarine Base. I reported aboard and about 3 months later we left for a WestPac tour. When we came back it was to Mare Island for new batteries as part of a Guppy I refit. I returned in 1998 after a 2-year commuter relationship with the lady who is now my wife: could not get her to move to AZ, so I moved here. No regrets, for a lot of reasons.
Sept, as I'm sure you know, is a great time of year to visit. What ship?
We'll have to agree to disagree on the "reserved word" (or at least "keyword") issue, I guess. I'm a great believer in forming programming habits and sticking with them, for consistency.
I'm also a great believer in what I think of as the ultimate galactical truth of database schema design: decisions about what should be put into what tables is never a matter of judgment about "too much" or "seems right", but rather it is based on straightforward analysis of what segment of the real world you are trying to model in your database. You must always begin by identifying all the entities you need to represent; these map into your tables. Then you identify the relationships among the entities. Then you identify all the properties of each entity; these map into the columns. I would be naive if I didn't admit that sometimes there are fuzzy areas, but if you strictly pursue this analysis, you will nearly always come up with the correct schema, and rather quickly. I've followed this methodology for many years when consulting and teaching and it is the only approach I would ever use or recommend.
Aha! A submariner! That explains a lot! [just kidding! some of my best friends...] I was in the amphib forces during the Korean conflict. The reunion is for USS Electra (AKA 4). I'm the founder and webmaster for http://usselectra.org. If I may brag about our site, we have the most extensive collection of photos, documents, memorabilia, crew database, of any single ship site that I know of, thanks to contributions of same from many former crew members from WWII and Korea.
I'm also a great believer in what I think of as the ultimate galactical truth of database schema design: decisions about what should be put into what tables is never a matter of judgment about "too much" or "seems right", but rather it is based on straightforward analysis of what segment of the real world you are trying to model in your database. You must always begin by identifying all the entities you need to represent; these map into your tables. Then you identify the relationships among the entities. Then you identify all the properties of each entity; these map into the columns. I would be naive if I didn't admit that sometimes there are fuzzy areas, but if you strictly pursue this analysis, you will nearly always come up with the correct schema, and rather quickly. I've followed this methodology for many years when consulting and teaching and it is the only approach I would ever use or recommend.
Aha! A submariner! That explains a lot! [just kidding! some of my best friends...] I was in the amphib forces during the Korean conflict. The reunion is for USS Electra (AKA 4). I'm the founder and webmaster for http://usselectra.org. If I may brag about our site, we have the most extensive collection of photos, documents, memorabilia, crew database, of any single ship site that I know of, thanks to contributions of same from many former crew members from WWII and Korea.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Well, you clearly are more of an expert than I so, while we will continue to disagree, I will admit that I am on the short end of the stick. Your solution was right on the money, so it's easy to tell who knows what they are talking about, and it isn't me.
Yes, not only a submariner, but a diesel boat sailor. We were all crazy and I suspect that the insanity is permanent. My wife keeps getting clients who were in the boats (exclusively nuclear these days) and she always tells them, "Oh yeah, my husband was one of you. Say no more, I know all about you. Don't worry, we'll work around that and get you a job anyway."
Nice website. Big ship. Of course when your main deck is a mere 3' above the water pretty much any ship looks big.
Yes, not only a submariner, but a diesel boat sailor. We were all crazy and I suspect that the insanity is permanent. My wife keeps getting clients who were in the boats (exclusively nuclear these days) and she always tells them, "Oh yeah, my husband was one of you. Say no more, I know all about you. Don't worry, we'll work around that and get you a job anyway."
Nice website. Big ship. Of course when your main deck is a mere 3' above the water pretty much any ship looks big.