Sql query

[James Page]

Hi all

Can anyone give me a starting point on how to acheive this:

I have two sql tables

Table 1:
Id
VehicleName
VehicleSpec
Registration
Year

Table 2:
Id
VehicleId
Photo
PhotoMIME
PhotoFilename

Table 1 has a relationship with table 2 (table1.Id - Table2.VehicleId)
Table 2 has many photos per vehicle (about 5-10)

I need to select all the vehicles from table1 but only return the first
photo for each vehicle from table2 and populate a GridView with the result.

Can anyone give me an example on how to acheive the above query?
I'll be using a stored procedure using VB.net & VS2008


Many thanks

 

[Gregory A. Beamer]

Hi all

Can anyone give me a starting point on how to acheive this:

I have two sql tables

Table 1:
Id
VehicleName
VehicleSpec
Registration
Year

Table 2:
Id
VehicleId
Photo
PhotoMIME
PhotoFilename

Table 1 has a relationship with table 2 (table1.Id - Table2.VehicleId)
Table 2 has many photos per vehicle (about 5-10)

I need to select all the vehicles from table1 but only return the
first photo for each vehicle from table2 and populate a GridView with
the result.

Can anyone give me an example on how to acheive the above query?
I'll be using a stored procedure using VB.net & VS2008

Can you alter the table slightly? If so, I would add another column like
IsListDisplay for the VehiclePhoto table and make a constraint so only
one item per VehicleID can be that picture. You can then set it up so
the first picture entered becomes the list picture. If you do not want
to directly constrain, you can use a trigger that can accomplish
ensuring if another item is set as a list picture for a specific vehicle
ID, it is reset.


Why this direction? Two-reasons. First, it makes your query very simple.
Second, it allows a user to choose something other than the first
picture uploaded.

If you can do this, the query is:

SELECT *
FROM Vehicle v
JOIN VehiclePicture vp
ON v.id = vp.vehicleid
AND vp.IsListDisplay = 1

If you go the other route, you are better creating a stored procedure or
function to return the listing and calling it from your code, as the
basics of returning one child per parent involve building up a result
set or creating a result set and then removing items that do fit the
"first one" rule.

 

[James Page]

Thanks Gregory

I'd cross posted this in the SQL forum and the responses were way over my
head!

Your suggestion is the easiest to implement

Thank you.

 

[Guest]

Thanks Gregory

I'd cross posted this in the SQL forum and the responses were way over my
head!

Your suggestion is the easiest to implement

Thank you.

The easiest way is definitely here

select t1.id, min(t2.Photo) from table1 t1
left join table2 t2
on t1.id=t2.VehicleId
group by t1.id

 

[Guest]

The easiest way is definitely here

select t1.id, min(t2.Photo) from table1 t1
left join table2 t2
on t1.id=t2.VehicleId
group by t1.id

P.S.

this is what Mark commented by depending on what you mean by "first"

 

[Gregory A. Beamer]

The easiest way is definitely here

select t1.id, min(t2.Photo) from table1 t1
left join table2 t2
on t1.id=t2.VehicleId
group by t1.id

In a single query, I would use something similar to what you suggest or
pare down the result sin a subquery, depending on what the goal was and
whether the photo field is a binary or simply a path, as I believe min-
ing on a binary field would be a bit interesting. ;-)

Overall, however, there is generally some type of intent in a list that
can then go to detail. And, while it may not be a specification today,
adding the list photo bit allows the user to state intent.

 

[Gregory A. Beamer]

news:1a7734bc-4743-4ccb-97d4- (e-mail address removed).
..


And, if the t2.Photo field is the actual binary image, the above will
give some interesting results...

But probably okay, since there is no intent in the listing, just "show
one picture". ;-)