MySQL Issue

The following query fails:

(SELECT DISTINCT lname, fname, number, street, phone, cell, comm, confno from resv where lname like 'Michael%' and comm <> 'Sleep Inn') UNION (SELECT DISTINCT lname, fname, number, street, phone, cell, comm, confno from resva where lname like 'Michael%' and comm <> 'Sleep Inn') order by lname, fname, date

with the message "unknown column date".

The structure of both tables resv & resva are identical. See attached image for structure. Both have date as the first column. (Note; the image does not show all columns, it is too long to capture with ONE screen shot).

What's wrong?
resv-str.JPG
Richard KortsAsked:
Who is Participating?
 
g3nu1n3Connect With a Mentor Commented:
If I am not mistaken, the issue is the select and union statements do not pull in the date column so therefore you cannot use it to order by. To confirm, remove date from the end and run the query, if that works, include date in your select statement and then add date back to your order by filter and see if that works which it should.
0
 
Richard KortsAuthor Commented:
To g3nu1n3

I see & that makes sense.

The problem is I DO NOT want a "SELECT DISTINCT ..." to include the date, because there are potentially MANY dates to which the other criteria apply.

I MIGHT be able to eliminate the order by date; I have to think of the ramifications.

Can you combine SELECT distinct with just plain select in a compound select statement like this?
0
 
ralmadaCommented:
try using group by

select lname, fname, number, street, phone, cell, comm, confno, max(date) as mdate
from (
	SELECT  lname, fname, number, street, phone, cell, comm, confno, date
	from resv 
	where lname like 'Michael%' and comm <> 'Sleep Inn'
	UNION 
	SELECT lname, fname, number, street, phone,  cell, comm, confno, date
	from resva 
	where lname like 'Michael%' and comm <> 'Sleep Inn'
) a
group by lname, 
      fname, 
      number, 
      street, 
      phone, 
      cell, 
      comm, 
      confno

Open in new window

or


(SELECT 
	lname, 
	fname, 
	number, 
	street, 
	phone, 
	cell, 
	comm, 
	confno,
	max(date) as mdate 
from resv 
where lname like 'Michael%' and comm <> 'Sleep Inn'
group by lname, 
	fname, 
	number, 
	street, 
	phone, 
	cell, 
	comm, 
	confno) 

UNION 

(SELECT 
	lname, 
	fname, 
	number, 
	street, 
	phone, 
	cell, 
	comm, 
	confno,
	max(date) as mdate
from resva 
where lname like 'Michael%' and comm <> 'Sleep Inn'
group by lname, 
	fname, 
	number, 
	street, 
	phone, 
	cell, 
	comm, 
	confno) 
order by lname, fname, date

Open in new window

0
 
Richard KortsAuthor Commented:
I realized that the date was kind of superfluous to the intent of the query so I removed it & vwalla, worked perfectly.

Thanks
0
 
g3nu1n3Commented:
Awesome, glad I could be of assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.