SQL: Union

[rinneii]

I'm trying to list both Student and Faculty information using the Union operator. This is what I have so far:

SELECT FacNo, FacFirstName, FacLastName
From Faculty
UNION Select StdNo, stdFirstName, stdLastName
From Student;

When I ran it, all that displayed was the Faculty information. Should I be using Union All?

 

[Joe4]

Are their duplicates between the two tables?
Do the corresponding fields all have the same data types?
What do the FacNo and StdNo values look like?

 

[rinneii]

I didn't see any duplicates, and the fields have the same data types. FacNo and StdNo are short text with 9 characters (all numbers).
I'm not sure if this would be an issue, but the Student table was created in Access, and the Faculty table was made in Microsoft SQL Server Management Studio. They are linked and I used the ODBC database import.

 

[Joe4]

I cannot recreate the scenario.

What happens if you run each one individually, like this:

Query1:

[COLOR=#333333]SELECT FacNo, FacFirstName, FacLastName[/COLOR]
[COLOR=#333333]From Faculty;[/COLOR]

Query2:

[COLOR=#333333]Select StdNo, stdFirstName, stdLastName[/COLOR]
[COLOR=#333333]From Student;[/COLOR]

Do both queries return data?

If so, could you post small sample of each data table?

 

[rinneii]

I'm sorry, but how do I post a sample? I read the 2 stickies at the top of the forum, but the link to download the add-in no longer works. Should I just copy the SQL from the Student and Faculty tables for you to try?

 

[Joe4]

From Access, you can just copy/paste from query/tables, like this:

1	4/27/2018	Adam	MUS 5 - BULK - PAC
2	4/27/2018 4:00:00 PM	Ben	MUS 5 - BULK - PAC
3	4/27/2018 6:00:00 PM	Cory	MUS 5 - BULK - PAC
4	4/28/2018	Ben	MUS 5 - BULK - PAC
5	4/28/2018 9:00:00 AM	Ben
6	4/28/2018 11:11:00 AM	Doug
7	4/29/2018	Edie
8	4/29/2018 10:00:00 PM	Cory
9	5/5/2018	Mora

<caption> Table1 </caption><thead>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] MyDate [/TH]
[TH="bgcolor: #c0c0c0"] Student [/TH]
[TH="bgcolor: #c0c0c0"] MyField [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Just go into the table/query, click on the box in the upper left corner to select everything, and copy/paste.
If there is too much data in your table to do that, create a query to select a small subset of records and do that.

 

[rinneii]

109876542	JUDITH	SMITH	ACC	PROF	86000		6/15/2008	49024
109876543	JUDITH	SMITH	MGT	PROF	81000		6/30/2008	49024
210987654	VICKIE	MORRIS	MKT	PROF	85000		7/11/1999	49034
321098765	JENNY	NOLAN	MKT	ASST	40000	210987654	3/1/2001	49035
432109876	JUDY	WILLIAMSON	FIN	ASSO	75000	765432109	3/15/2002	49142
543210987	VICTORIA	EMMANUEL	BIS	PROF	120000		4/15/1998	49111
654321098	LEONARD	FIBON	BIS	ASSO	70000	543210987	5/1/1996	49012
654422088	SONG	CARRIE	BIS	ASST	125000	543210987	8/1/2017	49008
765432109	NICKI	MACON	FIN	PROF	65000		4/11/1999	49035
876543210	CRISTOPHER	COLAN	MKT	ASST	40000	210987654	3/1/2001	49009
987654321	JULIA	MILLS	FIN	ASSO	75000	765432109	3/15/2002	49024

<caption>This is what the tables look like normally
Faculty </caption><thead>
[TH="bgcolor: #c0c0c0"] FacNo [/TH]
[TH="bgcolor: #c0c0c0"] FacFirstName [/TH]
[TH="bgcolor: #c0c0c0"] FacLastName [/TH]
[TH="bgcolor: #c0c0c0"] FacDept [/TH]
[TH="bgcolor: #c0c0c0"] FacRank [/TH]
[TH="bgcolor: #c0c0c0"] FacSalary [/TH]
[TH="bgcolor: #c0c0c0"] FacSupervisor [/TH]
[TH="bgcolor: #c0c0c0"] FacHireDate [/TH]
[TH="bgcolor: #c0c0c0"] FacZip [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

123456789	HOMER	WELLS	CIS	FR	3	49008
124567890	BOB	NORBERT	FIN	JR	2.7	49035
234567890	CANDY	KENDALL	ACC	JR	3.5	49142
345678901	WALLY	KENDALL	CIS	SR	2.8	49003
456789012	JOE	ESTRADA	FIN	SR	3.2	49001
567890123	MARIAH	DODGE	CIS	JR	3.6	49024
678901234	TESS	DODGE	ACC	SO	3.3	49036
789012345	ROBERTO	MORALES	FIN	JR	2.5	49001
876543210	CRISTOPHER	COLAN	CIS	SR	4	49014
890123456	LUKE	BRAZZI	CIS	SR	2.2	49006
901234566	WILLIAM	PILGRIM	CIS	SO	3.8	49035

<caption> Student </caption><thead>
[TH="bgcolor: #c0c0c0"] StdNo [/TH]
[TH="bgcolor: #c0c0c0"] StdFirstName [/TH]
[TH="bgcolor: #c0c0c0"] StdLastName [/TH]
[TH="bgcolor: #c0c0c0"] StdMajor [/TH]
[TH="bgcolor: #c0c0c0"] StdClass [/TH]
[TH="bgcolor: #c0c0c0"] StdGPA [/TH]
[TH="bgcolor: #c0c0c0"] StdZip [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

This was when I ran each of them separately (using the query)

109876542	JUDITH	SMITH
109876543	JUDITH	SMITH
210987654	VICKIE	MORRIS
321098765	JENNY	NOLAN
432109876	JUDY	WILLIAMSON
543210987	VICTORIA	EMMANUEL
654321098	LEONARD	FIBON
654422088	SONG	CARRIE
765432109	NICKI	MACON
876543210	CRISTOPHER	COLAN
987654321	JULIA	MILLS

<caption> Faculty </caption><thead>
[TH="bgcolor: #c0c0c0"] FacNo [/TH]
[TH="bgcolor: #c0c0c0"] FacFirstName [/TH]
[TH="bgcolor: #c0c0c0"] FacLastName [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

123456789	HOMER	WELLS
124567890	BOB	NORBERT
234567890	CANDY	KENDALL
345678901	WALLY	KENDALL
456789012	JOE	ESTRADA
567890123	MARIAH	DODGE
678901234	TESS	DODGE
789012345	ROBERTO	MORALES
876543210	CRISTOPHER	COLAN
890123456	LUKE	BRAZZI
901234566	WILLIAM	PILGRIM

<caption> Student </caption><thead>
[TH="bgcolor: #c0c0c0"] StdNo [/TH]
[TH="bgcolor: #c0c0c0"] stdFirstName [/TH]
[TH="bgcolor: #c0c0c0"] stdLastName [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

 

[Joe4]

Can you confirm the data types of the following two fields: FacNo, StdNo

 

[rinneii]

FacNo is ShortText
StdNo is ShortText

(also thanks for the help on sharing table information!)

 

[james_lankford]

try this

Select 
  stdLastName, 
  'student' as souce_table
From 
  Student


union 


SELECT 
  FacFirstName, 
  'faculty' as souce_table
From 
  Faculty