I am trying to output the same field twice in my SELECT but with different criteria for that field in the same row.
If I had to write it out in incorrect SQL syntax, but hopefully illustrates my point
SELECT names.FirstName WHERE xyz, names.FirstName WHERE abc FROM names;
I want the First Name column to repeat in the same row, but to SELECT using different criteria
.
The actual example is as follows, I have two tables,
names - unique ID, first name, last name
+----+---------+-------+
| ID | First | Last |
+----+---------+-------+
| 1 | Husband | Hlast |
| 2 | Wife | Wlast |
| 3 | Friend | Flast |
+----+---------+-------+
and another table,
groups - Unique ID , Unique ID of the head of house
+----+-------+
| ID | HOHID |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
+----+-------+
I would like the output to be the listing of each person's First and Last name as well as the First and Last name of their head of house
+----+---------+-------+----------+---------+
| ID | First | Last | HOHFirst | HOHLast |
+----+---------+-------+----------+---------+
| 1 | Husband | Hlast | Husband | Hlast |
| 2 | Wife | Wlast | Husband | Hlast |
| 3 | Friend | Flast | Friend | Flast |
+----+---------+-------+----------+---------+
I can get the head of households First and Last
SELECT names.First, names.Last WHERE names.id=groups.HOHid FROM names, groups;
and separately I can get the individuals first and last
SELECT names.First, names.Last WHERE names.id=groups.id FROM names, groups;
but I can't figure out how to join the two as separate columns as the output would need to be names.First and names.Last twice but with two separate WHERE clauses. I am very new to sql and any help would be appreciated.
