UNION Query to join 2 columns in the "same" table? 2

[kermitforney]

Is this possible?

I have never writtin a Union query but, what I need to do is join 2 columns in the same table. It has an Inner Join with another table that I need in the Union query as well.

I have an employer table that takes in employer info from 2 individuals. But when I run the report i need to combine both of their Employer cities(and other info like zip codes, county, and industry. Etc . .).

If it is possible can you post the SQL code. I like to write out my queries instead of relying on the Design Grid.

Details:

Tables
tblCommunity and tblEmployer

INNER JOIN
tblCommunity.cmID, tblEmployer.cmID

Columns (need to be joined)
tblEmployer.emEmployerCity and tblEmployerCity2

Also after this happens I need to run a count EmployerCity.

((Sorry for the lengthy post but I have been working 13 hour days and I have yet to find a fix for "this" problem among others ) ))

Thanks guys.

 

[kermitforney]

Here is another question I would like to attach to this thread.
From a DB design aspect would it be better to just lump all of the employers together and, then use an identifier to differentiate employercity1 from employercity2?

 

[AlexCuse]

from design perspective I think you are OK. You are not really very clear on what you want your query to accomplish though.

What is the relationship between EmployerCity1 and EmployerCity2?

I think you will need a join to a subquery but it is hard to tell.

HTH,

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[kermitforney]

I need to combine them so that I can analyze the data.
In other words, I need to lump all of the cities of Employment together and display the results in a report.

So I need "TOTAL" Employer cities not EmployerCity1 and Employercity2 seperately.

Let me know if that doesn't clarify it. )

 

[lespaul]

can you post some sample data from your tables and your expected results?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kermitforney]

Here goes lespaul! )

Table:
tblCommunity
Columns:
cmID, cmCommunity

Table:
tblEmployer
Columns:
cmID, emEmployerCity, emEmployerCity2

(There is an existing INNER JOIN between tblCommunity and tblEmployer ON tblCommunitycmID=tblEmployer.cmID)

I would like both (emEmployerCity and emEmployerCity2) columns to join together to create one column.

So I this is what I need the query to do:

1.) Make sure tblCommunity.cmID=tblEmployer.cmID

2.) Then make sure Community= "Siena Hills"

3.) Then UNION tblEmployer.emEmployerCity AND tblEmployer.emEmployerCity2

Is that more clear?

 

[Golom]

Lets see if I can restate it.

You have two columns EmployerCity1 and EmployerCity2 and you want to combine those into a single column and then count the number of records in that combined column.

If that's more or less correct
Counting DISTINCT Cities Only

Select Count(*) As [Distinct Cities]

From

(Select EmployerCity1 As [City] From myTable
 UNION
 Select EmployerCity2 From myTable)

Counting ALL Cities Including Duplicates

Select Count(*) As [All Cities]

From

(Select EmployerCity1 As [City] From myTable
 UNION ALL
 Select EmployerCity2 From myTable)

 

[lespaul]

just in case Golom didn't get it right, what really helps in creating queries is more like:

Table:
tblCommunity
Fields:
cmID cmCommunity
1 Siena Hills
2 Something else
3 The Valley

Table:
tblEmployer
Fields
cmID emEmployerCity emEmployerCity2
1 Somewhere Somewhere else
2 Nowhere Nowhere else

results:

some list of fields
what you want the above data to look like after running query


Thread701-1278865 has an example (here's what I've got....here's what I want)

les

 

[kermitforney]

Thanks Golom!

I will try it out in a few minutes . . or after lunch!

Only problem is I NEED that inner join in there. Can I just add it in the FROM clause?

 

[Golom]

Could be, but I don't know enough about tblCommunity to be able to say if a JOIN is possible. The above UNION queries just produce a table with one column called CITY. If tblCommunity doesn't have a column containing city names then there's no logical Join to be had.

You need a field that's common to the two tables (or derived tables) to make a join possible.

Another possibility is to use one of the above queries to supply a single field value as in this example

Select fld1
     , fld2
     , ...
     , (Select Count(City) From
          (Select EmployerCity1 As [City] From myTable
           UNION ALL
           Select EmployerCity2 From myTable)) As [CityCount]
     , ...

From [red]... and do your table joins here ...[/red]

 

[kermitforney]

tblCommunity
|cmID| cmCommunity |
1|0001| Sienna Hills |
2|0002| Algorve |
3|0003| Sienna Hills |
4|0004| Ruby Hills |
5|0005| Ethereal |
6|0006| Sienna Hills |

tblEmployer
|cmID|emEmployerCity |emEmployerCity2|
1|0001| Los Angeles | Brentwood |
2|0002| Irvine | West Covina |
3|0003| Laguna Hills |Laguna Del Ray |
4|0004| Anaheim | Downey |
5|0005| Rhonert Park | Petaluma |
6|0006| San Francisco | Sausalito |


So the join is on tblCommunity.cmID=tblEmployer.cmID.

What I need the Union to do is combine both emEmployerCity and emEmployerCity2. I need to know ALL of the employer cities from people who bought at Siena Hills even the duplicates.
Then I need a count ran on that data grouped by Cities.

So the result would look like this (or however the result would be returned):

|cmID| cmCommunity |emEmployerCity(1&2)|
1|0001| Sienna Hills | Los Angeles |
1|0001| Sienna Hills | Brentwood |
3|0003| Sienna Hills | Laguna Hills |
3|0003| Sienna Hills | Laguna Del Ray |
6|0006| Sienna Hills | San Francisco |
6|0006| Sienna Hills | Sausalito |

I have the JOIN, GROUP BY and the SELECT statements down but have never used UNION before and can't quite get it right.

THANKS for all of your help guys. I really apreciate it.

 

[Golom]

Ahhhh ... EXAMPLES!! Now that makes life easier.

Select A.cmdID
     , A.cmCommunity
     , B.City As [emEmployerCity(1&2)]

From tblCommunity A
     INNER JOIN

     (Select cmdID, emEmployerCity As City
      From tblEmployer Where emEmployerCity IS NOT NULL
      UNION ALL
      Select cmdID, emEmployerCity2
      From tblEmployer Where emEmployerCity2 IS NOT NULL) As B

     ON A.cmdID = B.cmdID

Where A.cmCommunity = 'Sienna Hills'

This will retain duplicate cities for a cmdID. If you want only a single occurrence of each city then change [blue]UNION ALL[/blue] to just [blue]UNION[/blue].

Just drop the WHERE clause to see all of the communities or use some data source from your form (e.g. text box, combo box) to insert the criteria for the community that you want to see.