[SOLVED] Help on multiple table joins!

[keyurshah]

Hey freaks family, howz ya'all. I need a small help.

 

I have four tables like make, model, version and prices.

 

Make table has two cols makeid (PK) and makename.

 

Model table has three cols modelid (PK) makeid (FK) and modelname.

 

Version table has 10+ cols but for relating with above two, the cols are versionid(PK), modelid (FK) and versionname

 

The Price table has cols such as pid(PK), makeid, modelid, versionid, prcmum, prcdel, prcban and prcche.

 

Now supposing, a makeid is 6 and the makename is Ford. The make Ford (details in the make table) has 3 models (details in the model table) and each model has 4 versions (details in the version table)

 

By the makeid I need to get the max price and the min price from the price table of just one city ie (prcmum)

 

By doing this:

 

SELECT 
DISTINCT(nwveh_model.modelid), 
nwveh_make.makename, 
nwveh_make.makeid, 
max(case when nwveh_exshowroomprices.prc_mum!=0 or nwveh_exshowroomprices.prc_mum!=null or nwveh_exshowroomprices.prc_mum!='-NA-' then nwveh_exshowroomprices.prc_mum end ) AS maxprice, 
min(case when t.prc_mum!=0 or t.prc_mum!=null or t.prc_mum!='-NA-' then t.prc_mum end ) AS minprice 
FROM nwveh_model 
INNER JOIN nwveh_make ON nwveh_make.makeid=nwveh_model.makeid 
INNER JOIN nwveh_version ON nwveh_version.modelid=nwveh_model.modelid
INNER JOIN nwveh_exshowroomprices ON nwveh_exshowroomprices.modelid=nwveh_model.modelid 
INNER JOIN nwveh_exshowroomprices as t ON t.modelid=nwveh_model.modelid 
WHERE nwveh_model.makeid='6' and nwveh_model.modelstatus='Y' 
GROUP BY nwveh_model.modelname ORDER BY nwveh_model.modelname ASC

 

I get four records of each models max and min prices of models like this:

modelid makename makeid maxprice minprice

32 Ford 6 1860140 1604235

34 Ford 6 853956 657804

35 Ford 6 720000 625706

36 Ford 6 483000 483000

 

what I actually want is just the max and the min price and make id, like this

makeid maxprice minprice

6 1860140 483000

 

Please help me change my query so that I can get my desired results!

 

Help Family!

 

Thanks in advance!

[fenway]

I'm confused as to why you don't just group by both columns.

[keyurshah]

Sorted out myself.

 

When you do a lot of coding you tend to make the simplest of the mistakes.

Here in my case, there was no need for joining all the tables. As I already had the makeid, I simply queried the price table (where makeid is the FK) using the makeid and then sorted out the results (max and min) and VOILA.

 

So sorry but anyways thanks fellas, who took the trouble to even view this thread.