?
Solved

MySQL Issue

Posted on 2012-09-13
5
Medium Priority
?
408 Views
Last Modified: 2012-09-13
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
0
Comment
Question by:Richard Korts
  • 2
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
g3nu1n3 earned 2000 total points
ID: 38395931
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
 

Author Comment

by:Richard Korts
ID: 38395993
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38396254
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
 

Author Closing Comment

by:Richard Korts
ID: 38396819
I realized that the date was kind of superfluous to the intent of the query so I removed it & vwalla, worked perfectly.

Thanks
0
 
LVL 6

Expert Comment

by:g3nu1n3
ID: 38397492
Awesome, glad I could be of assistance.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question