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