?
Solved

SQL and Distinct

Posted on 2009-05-06
8
Medium Priority
?
240 Views
Last Modified: 2012-05-06
I'm having a problem selecting using distinct and then doing a join. I am trying to just select one vehicle of a specific trim level if more than one exist but I do still  need all the other data from the two tables.

I know the below code is wrong but I'm just learning this stuff and would greatly appreciate your assistance.
Thanks!  
$query_CRV = "SELECT *, DISTINCT Vehicle_Additional_Information.Trim " .
"FROM ebizcardata " .
"INNER JOIN Vehicle_Additional_Information " .
"ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No " 
"WHERE Modell = 'Accord' AND New_Used = 'New' AND Ext_Color = 'Taffeta White' 
OR Modell ='Accord' AND New_Used = 'New' AND Ext_Color = 'White Diamond Pearl'" .  
"ORDER BY Year ASC,  STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC";

Open in new window

0
Comment
Question by:Cutthroat_Trout
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 24321224
Distinct applies to an entire query, and not just one field.  So, you'd want to use the following to select distinct.  I also added some parens in your where clause, otherwise your or statements will just "take over" and your other statements wont really matter :)

Also, DISTINCT is sometimes "costly", you might want to look into using GROUP BY instead when possible.

select distinct vehicle_additional_information.trim
  from ebizcardata
 inner join vehicle_additional_information
    on ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No
 where (Modell = 'Accord' 
   and New_Used = 'New' 
   and Ext_Color = 'Taffeta White')
    or (Modell ='Accord'
   AND New_Used = 'New' 
   AND Ext_Color = 'White Diamond Pearl')
 ORDER BY Year ASC,  STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC

Open in new window

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24321252
Slightly tuned out the query too.

Distinct is not required as we are doing an inner join on that table. And if you have duplicate records in the below SELECT query then use

SELECT distinct ebizcardata.*,

instead of

SELECT ebizcardata.*,
SELECT ebizcardata.*, Vehicle_Additional_Information.Trim 
FROM ebizcardata 
INNER JOIN Vehicle_Additional_Information 
ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No 
WHERE Modell = 'Accord' AND New_Used = 'New' 
AND Ext_Color IN ('White Diamond Pearl','Taffeta White')
ORDER BY Year ASC,  STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC

Open in new window

0
 

Author Comment

by:Cutthroat_Trout
ID: 24321355
Am I not asking the question right? Both of your examples work (well kind of) but not still do not give me what I am looking for. I need all the data from both the tables (I use it later in the code) but I do not want to show the duplicated trim column results.  

Thanks again for the help.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24322820
I believe what you want is the TOP opeerator not Distinct.  The first query below will give you just the first row that match your criteria, but all columns from both tables.
If it is that you only want the top 1 row from the Vehicle_Additional_Information table and all the values from the other table, ebizcardata, then you will have to the query something like the second example where you make the Vehicle_Additional_Information into a subselect that uses TOP 1 to get the first record from there and match it to the other table.
$query_CRV = "SELECT TOP 1 * " .
"FROM ebizcardata " .
"INNER JOIN Vehicle_Additional_Information " .
"ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No " 
"WHERE Modell = 'Accord' AND New_Used = 'New' AND Ext_Color = 'Taffeta White' 
OR Modell ='Accord' AND New_Used = 'New' AND Ext_Color = 'White Diamond Pearl'" .  
"ORDER BY Year ASC,  STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC";
 
$query_CRV = "SELECT * " .
"FROM ebizcardata " .
"INNER JOIN (SELECT TOP 1 * FROM Vehicle_Additional_Information ".
"    WHERE conditions_that_limit_this_table=values) Vehicle_Additional_Information " .
"ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No " 
"WHERE Modell = 'Accord' AND New_Used = 'New' AND Ext_Color = 'Taffeta White' 
OR Modell ='Accord' AND New_Used = 'New' AND Ext_Color = 'White Diamond Pearl'" .  
"ORDER BY Year ASC,  STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC";

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24322845
i think you need this.
SELECT e.*, v1.Trim 
  FROM ebizcardata e
 INNER JOIN Vehicle_Additional_Information v1 
    ON e.Stock_No = v1.Stock_No 
 INNER JOIN (SELECT Stock_No,MAX(Trim) AS Trim FROM Vehicle_Additional_Information GROUP BY Stock_No) AS v2
    ON v1.Stock_No = v2.Stock_No AND v1.Trim = v2.Trim
 WHERE Modell = 'Accord' AND New_Used = 'New' 
   AND Ext_Color IN ('White Diamond Pearl','Taffeta White')
 ORDER BY Year ASC,  STR_TO_DATE(Date_In_Stock, '%m/%d/%Y') ASC

Open in new window

0
 

Author Comment

by:Cutthroat_Trout
ID: 24328233
Well thanks for your efforts. It still does not do what I was attempting to do but I did learn some ways to clean up the initial statement.  So I'll close this question.

The problem still exist. Duplicate trims are returned.

Thanks any way.
0
 

Author Closing Comment

by:Cutthroat_Trout
ID: 31578791
I can't figure out how to split up the points and you IN clause was the most helpful tip I learned by asking this question even though my original problem still exists.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24331628
If you still need assistance, post the result of my query and your expected result.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
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

801 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