Can use some help with a select query

Hi,

I have this tables (view attachment) wich i want to query with a selct statement I'll tried al sort of things but it didn't happend!

I have a machine which has one cabinet in this cabinet there could be 2 screens , I want the details of both screens of a certain machine in a single line
Hope somesone understand what i want!

Cheers WimmoAs a matter of fact, no I don't understand.
It sounds like you are describing a crosstab query, but your schema allows only one screen per cabinet.
It would be best if you posted the query you have tried, and let us know how the results differed from what you wanted.|||Untested:
select *
from tblMachine m
,tblCabinet c
,tblScreens s1
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number|||Untested:
select *
from tblMachine m
,tblCabinet c
,tblScreens s1
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Numberi would be extremely leery of mixing "comma list" syntax with JOIN syntax

in mysql 5, for instance, JOINs take precedence (similar to the way ANDs take precedence over ORs) and so the following will produce an error --tblScreens s1 left join tblScreens s2
on c.Screen2=s2.Part_Numbercan you see why?|||Yes the left join should be to tblCabinet i.e.
select ...
from tblMachine m
, tblScreens s1
, tblCabinet c
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number

And as you say this is just as bad as mixing ANDs and ORs without brackets
Thanks for highlighting it
So here it is without mixing syntax
select ...
from tblMachine m
,tblCabinet c
,tblScreens s1
,tblScreens s2
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number
and c.Screen2*=s2.Part_Number

select ...
from tblMachine m
join tblCabinet c on m.Cabinet=c.id
join tblScreens s1 on c.Screen1=s1.Part_Number
left join tblScreens s2 on c.Screen2=s2.Part_Number|||Hi All, thanx for your reply's.
I tried the query's but none did actually worked, they generated no errors but it returned zero records where ther should be one.

@.Blindman, I tried this query for getting the info of 1 screen which is already hard to get but there could be 2 or none in a cabinet

SELECT tblCabinet.Screen1, tblCabinet.Screen2, tblScreenTypes.ScreenType, tblBrands.BrandName, tblCommTypes.CommType, tblAdaptor.Adaptor
FROM tblMachine INNER JOIN
tblCabinet ON tblMachine.Cabinet = tblCabinet.ID INNER JOIN
tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number INNER JOIN
tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID INNER JOIN
tblBrands ON tblScreens.Brand = tblBrands.ID INNER JOIN
tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID INNER JOIN
tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
where tblMachine.Part_NUmber = 'Value'

I'll tried several changes in the joins but none returned an error but none returned values.|||If your query unexpectedly returned zero rows, then run this and see how many rows it returns:SELECT count(*)
FROM tblMachine
-- INNER JOIN tblCabinet ON tblMachine.Cabinet = tblCabinet.ID
-- INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number
-- INNER JOIN tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID
-- INNER JOIN tblBrands ON tblScreens.Brand = tblBrands.ID
-- INNER JOIN tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID
-- INNER JOIN tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
where tblMachine.Part_NUmber = 'Value'
Then, uncomment one line at a time until your query again returns zero rows, and that will tell you where the problem join is.|||I'll did what you suggested and at this line

INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number

it returns 0 but i don´t understand why, there are values. Could it have something todo with the relation?

Thanks to your proposed strategy i found the problem, seems that there was a relation to an old table on screen 1 so deleting that table did solve my problem.

Thanks for all your help and probably expensive time.

Wim

Can sqldatasource automatically sort retrieved data from stored procedure ?

Hi,

This is my problem :

I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2.

I wrote one stored procedure to retrieve data from my sql database like :

select Id, Name,Address,Amount from KalakaDB

Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order

how can i connect my sqldatacontrol to the stored procedure ?

Thanks

Typically sorting is controlled by the data bound control (for example, the GridView). However, the sorting is actually performed by the data source. In this case if you don't have a way to tell the data bound control which sort express to use, you can do it directly on the data source by handling SqlDataSource's Selecting event. In that event, simply say something like:

e.Arguments.SortExpression = "Amount DESC";

Thanks,

Eilon

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>