Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ctvogds
Starting Member
6 Posts |
 Posted - 2005-04-26 : 14:49:55
|
| Ok so my boss at work wants me to select a whole bunch of data from like 30 tables. This data is to be compiled into a table for her to view and analyze. The problem is that she wants field 'A' from table 'TA' and field 'B' from table 'TB' 'C' from 'TC' etc... all in one table. Now all of our table data is keyed using a unique ID, all ID's in TA, TB, TC are a unique subset of our master set of ID's.The records in the new table are to be structured as follows, for every ID in our master set of ID's (these ID's are already in the new table) she wants the values from fields A,B,and C displayed. If there is not a value for A,B, or C then she wants just a blank space displayed. I thought that this may be accomplished through a FULL UNION join, however not all of the tabled TA, TB, TC have the same number of records, which an access UNION requires.Short of creating a new table from hand and running a ton of INSERT INTO'S is there a way to do this?? Perhaps all in one statement? |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-04-26 : 15:36:44
|
| I think you're mixing your terminology a little bit here. UNION and JOIN are two very different operations. Yes, UNION requires that each table in the UNION have the same number and types of fields in their SELECT statements, and any that are missing have to be filled in with filler formula to make them fill out to the same.However, it sounds like your project can be done with JOINs instead. Something along the lines of:SELECT Master.ID, TA.A, TB.B, TC.CFROM MasterIDTable as MasterLEFT JOIN TA ON Master.ID = TA.MasterIDLEFT JOIN TB ON Master.ID = TB.MasterIDLEFT JOIN TC ON Master.ID = TC.MasterIDetc., etc.---------------------------EmeraldCityDomains.com |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-27 : 18:21:46
|
| you need to provide some detailed examples of what you need to do. Not code or anything, just give us a good sampling of data that represents your problem and then based on that sample data the results you are trying to acheive. If you'd like some help here, you are going to have to be much more specific. That way we can focus on trying to figure out the answer as opposed to trying to guess what your question is.- Jeff |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-04 : 21:56:24
|
| In Access, you would create 2 queries to get the job done, one pulling from the results of the first:query1: just select the ID from each table, all unioned together, and GROUP BY ID as well to return 1 unique row per id. this is your "master" list of ID values. (Note: think about normalizing your data and permanently storing this list in a master table that defines each ID value, along with any other attributes that belong there. A good DB design saves so many headaches like this. )query2: Select ID from Query1, and then left outer join to each of the 3 tables on the ID column. Return all columns from the outer tables as necessary.>>I am pretty damn sure that this is just a normal UNION join for most DBMS but as those of you more experienced people know Access only allows unions on tables with the same numbers of records and clearly Table 1 has fewer records than Tables 2 and 3.a) you are confusing "records" with columns or fields. Be sure to understand the difference. the UNION operator in any RDMS does not care about the number of records, it cares about the number of COLUMNS.b) The UNION operator for all RDBM's (except maybe MySQL, who knows) requires that you explicitly line up all columns and that they match. otherwise, how can consistent results be returned?andc) As mentioned, the difficulties you are facing have nothing to do with MS Access but with your overall design. I get really peeved when people blame MS Access for "not working right" when they have a bad database design.- Jeff |
|
|
|
|
|
|
|
|
Joins or Unions??