Show data in datalist from 2 table

Question

2.50/5 (2 votes)

See more:

I Have Two Table Tbl_Car_detail where Column is

HTML

CarID(it is Private key)
CarBrand

and 2nd Table tbl_car_Image where Column is

HTML

ImageID(it is Private key)
CarID(it is foreign key of Tbl_Car_detail)
Image

Record in 1st table Tbl_Car_detail like this

HTML

CarID                CarBrand
1                       Audi

2                       bwm

i am inserting Multiple Image of same CarID in 2nd table tbl_car_Image like this

HTML

ImageID                  CarID             Image
1                             1                  Image1

2                             1                  Image2

3                             1                  Image3

4                              2                 Image4

5                              2                 Image5

Now i want show result in one datalist from both table
I am Using this sql Query

SQL

SELECT     dbo.Tbl_Car_detail.CarID,dbo.tbl_mst_CarDetail.CarBrand , tbl_car_Image.Image
FROM         dbo.Tbl_Car_detail.CarID RIGHT OUTER JOIN
                      tbl_car_Image.Image ON dbo.Tbl_Car_detail.CarID = dbo.tbl_car_Image.CarID

I am Geting record like this

HTML

CarID               CarBrand                Image

1                      Audi                       Image1

1                      Audi                       Image2

1                      Audi                       Image3

2                      Bmw                        Image4

2                      Bmw                        Image5

But i want only 1 image with per CarID result like this

HTML

CarID               CarBrand                Image

1                      Audi                 Image1

2                     Bmw                   Image4

Please Provide me sql query, you can mail sql Query at [Edit]E-mail removed[/Edit]

Posted 10-Oct-12 9:51am

GipAnoj1988

Updated 10-Oct-12 9:57am

Kenneth Haugland

v2

Add a Solution

Comments

I.explore.code 10-Oct-12 15:55pm

You need to improve your question to stand any chance of help! In its current state it barely makes any sense. Plus, no one here does personal favours by sending customised solutions to you e-mail id. So you can forget that. You need to read up on joins and binding composite data to controls. One hint I would give you though is, "Use extenstion methods for your objects" they are the best fit for this kind of problems.

Kenneth Haugland 10-Oct-12 15:58pm

Yes, and don't post you e.mail here, unless you really like SPAM.

I.explore.code 10-Oct-12 16:06pm

oh damn! you removed the e-mail id, I was having ideas to spam him/her ;)

Sergey Alexandrovich Kryukov 10-Oct-12 16:09pm

You still can -- it's too late to hide, just see v1 :-)
--SA

I.explore.code 10-Oct-12 16:20pm

excellent point! guess you have some experience here :)

Sergey Alexandrovich Kryukov 10-Oct-12 16:35pm

:-)
Never for actual spamming, I swear, only for embarrassing; maybe for some blackmailing, but just a bit. :-)
--SA

Kenneth Haugland 10-Oct-12 16:12pm

Well, SPAM from an anonymous CP account would do just fine :-D

I.explore.code 10-Oct-12 16:21pm

its an overkill *YAWNS*!

Kenneth Haugland 10-Oct-12 16:23pm

The e-mail or the spam? or could it be both?

I.explore.code 10-Oct-12 16:35pm

now it seems both! :)

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Scott Clayton · Answer 1 · 2012-10-10T15:49:00

If I'm understanding your question, then you may just need to add a GROUP BY and then use the MAX function to get one of the image fields.

SQL

SELECT dbo.Tbl_Car_detail.CarID, dbo.tbl_mst_CarDetail.CarBrand, MAX(tbl_car_Image.Image)
FROM dbo.Tbl_Car_detail
RIGHT OUTER JOIN tbl_car_Image.Image
ON dbo.Tbl_Car_detail.CarID = dbo.tbl_car_Image.CarID
GROUP BY dbo.Tbl_Car_detail.CarID, dbo.tbl_mst_CarDetail.CarBrand

Or if you need to ensure that you're getting the top image for each group (order matters), then perhaps you can use a common table expression like this:

XML

WITH cte AS 
(
SELECT dbo.Tbl_Car_detail.CarID, 
       dbo.tbl_mst_CarDetail.CarBrand, 
       tbl_car_Image.Image, 
       ROW_NUMBER() OVER (PARTITION BY dbo.Tbl_Car_detail.CarID, dbo.tbl_mst_CarDetail.CarBrand ORDER BY tbl_car_Image.Image DESC) AS rownum
FROM dbo.Tbl_Car_detail
RIGHT OUTER JOIN tbl_car_Image.Image
ON dbo.Tbl_Car_detail.CarID = dbo.tbl_car_Image.CarID
)
SELECT CarID, CarBrand, Image
FROM cte
WHERE rownum = 1

I'll admit to not actually creating tables and running either of these queries, so you may need to tweak them before use.

Show data in datalist from 2 table

1 solution

Solution 1

Add your solution here

Preview 0

Show data in datalist from 2 table

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us