Question Need help with complex SQL query

[keb1965]

I need to query multiple tables in a database and return only some of the columns, but also return a column value based on the existence of a matching record in a different table.

Example:
I have 3 tables, table1, table2, and table3 each has numerous records

table1 and table2 correspond with the same number of records so a join is simple ... i.e.

SELECT table1.ID, table1.name, table1.age, table2.street, table2.state FROM table1, table2 WHERE table1.ID = table2.ID

But now I want to add another column from table3, and it needs to be between table1.age and table2.street ... but, I don't want the actual value stored in the column, but rather a string value based on whether a value exists for that field.

SELECT table1.ID, table1.name, table1.age, table3.spouse, table2.street, table2.state FROM table1, table2 WHERE table1.ID = table2.ID

in this example, I need table3.spouse to be filled with 'M' or 'S' based on whether there is a value in the field. Also note that table3 only has records where table3.spouse actually has a value, so there might be 100 records in table1 and table2, but only 30 records in table3.

I hope I am making sense ...

Finally, I want to add another field to my returned data that doesn't exist in any of the tables, but is predicated on the value of a field that does exist. For example, table1.age might be 30 or it might be 12 ... I want to add another field, say perhaps, drinkingage if table1.age > 20 then 'Y' else 'N'

I'd appreciate any help.

 

[jmcilhinney]

What database?

 

[keb1965]

The database in the sample files I have is sqllite version 3 but I can't be assured of the version.

Just to note a bit more information, the data will not need to be displayed and the datatable that I eventually load the data into will be there simply for the purpose of holding the rows until I can write them out to a comma delimited file, so worst case, I simply do the comparison tests on the data once it is retrieved from the database. The ideal situation would be that the data would be in a format that I can readily export to csv.

 

[cjard]

SELECT CASE WHEN t2.col IS NULL THEN 't2 is null' ELSE 't2 is not null' END as valueexists FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id

SELECT CASE WHEN age > 20 THEN 'Y' ELSE 'N' END AS drinkingage FROM table