Can't get SQL right

[FuZion]

Hello,

I have a query which needs to pull the just the row ID from a table, but the row must meet certain requirements. The information is in 3 tables, linked by IDs, ssid, aid and TID are the ones I used in the query.. the other table just uses ID.

Here is the query:
SELECT si.id FROM `trucks` t, `service_sheets` ss, `service_info` si WHERE ss.aid=t.aid AND ss.ssid=si.ssid AND ss.aid=0 AND ss.date=07/07/07 AND si.hours=2517 AND t.unit_num='22784' AND si.mileage=454857

The data in there is dummy data if you guys need to know what the table structure is let me know, I'm not very good with this complex SQL stuff.

Basically I want to pull the row id from the service_info table, in the service info table there is a service sheet id (ssid), which corresponds to the row in the service sheet table that this row belongs too. There is also a truck id (tid) that references the truck table so I can get the unit number.

Any help is greatly appreciated.

Thanks in advance!

FuZion

[tripy]

Your joints are looking ok...
Just 2 things:
1) use the date formating option to compare the date (assuming than the date field DO HAVE a date datatype
2) Numbers don't need quotes, only varchar, char and text/blob

Try that query:

SELECT si.id
FROM
trucks t,
service_sheets ss,
service_info si
WHERE ss.aid=t.aid
AND ss.ssid=si.ssid
AND ss.aid=0
AND date(ss.date)=date('07-07-2007')
AND si.hours=2517
AND t.unit_num=22784
AND si.mileage=454857


I make the assumption that every fields are numeric fields, except ss.date.
Correct the syntax (ie: put quotes) if those are alphanumeric datatypes

[FuZion]

I have tried that query also and no results have been returned, but I have triple check everything and an ID should be returned, so I'm not sure what is going on here. The datatypes are all correct, I am using a date field and all INTs except for the unit_num. I will keep playing with it, should I be using JOIN statements?

Thank you for your help!

[tripy]

should I be using JOIN statements?

You already are.
This syntax implicitly specify inner joins.

Try to verify if you don't have a table which miss datas.
Using inner joins, like this, if 1 table have no corresponding datas, nothing will be returned by the db.

If you have 1 or more tables which the datas should be matched if they exists, but still have the line coming out if there are no datas, an outer join will be needed, but this wil complexify and slow down your query a lot.

[FuZion]

So I have gotten rid of this line: ss.aid=t.aid

and added: AND t.aid = 1

so here is my query:

SELECT si.id FROM trucks t, service_sheets ss, service_info si WHERE ss.ssid=si.ssid AND ss.aid=1 AND t.aid=1 AND date(ss.`date`)=date('07-02-2007') AND si.hours=555 AND t.unit_num='123' AND si.mileage=88

would it help if I posted the data from the 3 tables?

I'm not sure which tables are missing the data it woul dhave to be service_sheets or service_info right? Does all the data have to match or just the values that are joined?

[tripy]

would it help if I posted the data from the 3 tables?
Not really, everything would be needed to be sure.

Try to populate your tables manually, so you know you have a valid dataset for this case.
Try to build your query around it.
Then, pick some random case in existing datas, and check if your query is still valid.

Your problem can be originating from your datas, or from your query.
You must check your datas to determine if they are correct first.

[FuZion]

The data is manually entered, it is all dummy data as of now. Am I correct with this statement:
it would have to be service_sheets or service_info right? Does all the data have to match or just the values that are joined?

Thanks a lot for your help!

[tripy]

I don't get what you mean by "it would have to be service_sheets" ...


Does all the data have to match or just the values that are joined?

With an inner join, which is what does the query I gave you, every joined values must match.
If 1 table miss the joining value, the record won't show in the output

so, in your exemple case:
service_sheets.aid must match at least 1 truck.aid
service_info.ssid must match at least 1 service_sheets.ssid
and the fields service_sheets.aid and service_sheets.ssid that make the joints must be on the same record

and for every rows matched by the previous
service_sheets.aid must be 0
service_sheets.date must be 07-07-2007
service_info.hours must be 2517
trucks.unit_num must be 22784
service_info.mileage must be 454857

Right now, if any of those conditions is not met, the record won't show.
So, if the tables are manually populated, be sure to have at least 1 set of valid datas.
You must first have coherent datas, before querying them.

[FuZion]

Ok I have put at least 10 different dummy rows and it is still coming up with nothing I have triple checked that the rows are correct and the data is correct. There must be something that I am not understanding here. Would you like to take a look at my databases?

[tripy]

Yep, I'm sure it would be helpful.

If you can give me a dump of your structure and datas, I could integrate them in my server and play with them