Select Statement. 1

[JustWondering]

Hello friends,

Please help me to make this complicate query:

I have 3 tables and want the records return from the first table with these conditions:

1. table1.id = table2.id = table3.id
2. table1.code = 4
3. table1.income > $10,000
4. table3.xcode =2 or=3

Here is what come up with:

Select * from table1, table2, table3
where table1.id = table2.id
and table2.id = table3.id
and table1.code = 4
and convert(float,table.income)>10000
and (table3.xcode=2 or table3.xcode=3)

Do you think this will do the work? When I ran it, the return records doesn't seem right to me. I'm not sure. Please give me some advise. Thanks.

 

[JustWondering]

Please disregard my previous question.

Right now I have:
Query1: Return records from table 1 that meet 2 conditions.
(cond1: table1.id = table2.id)
Query2: Return records from table 2 that meet 2 conditions.
(cond1: table2.id = table1.id)

How can I combine these 2 to make only 1 query?

I try something like this, but the result looks kinda unusual:

Select * from table, table2
where table1.id = table 2.id
and table1.fieldx = a condition
and table2.fieldx = another condition

Will this do the work for me?????

Thanks.

 

[billchris]

Maybe, try this

  SELECT
    t1.id,
    <other fields>
  FROM
    table1 t1
    inner join table2 t2 ON t1.id = t2.id
  WHERE
    t1.fieldx = <some condition>

  UNION ALL

  SELECT
    t2.id,
    <other fields>
  FROM
    table2 t2
    inner join table1 t1 ON t2.id = t1.id
  WHERE
    t2.fieldx = <some other condition>

You just have to make sure that the <other fields> for each select contain the same number of fields with the same data types in the same order. Check out the &quot;Union&quot; statement in the BOL.

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[JustWondering]

Thanks billchris,

May be my question was not clear. I don't the records from both tables. I just want return the records that have match id. Form table 1 I have 2 conditions and the id match condition. From table 2 just 1 condition and the match id condition.

Thanks

 

[JustWondering]

Anyone? Please help!

Both tables have an ID field. I want the records from both table that have same ID plus a couple conditions from eachone.

Thanks.

 

[billchris]

Ok, is this more what you are looking for?

SELECT
    t1.id,
    <other t1 fields>,
    <t2 fields>
  FROM
    table1 t1
    inner join table2 t2 ON t1.id = t2.id
  WHERE
    t2.<conditionField> = <table2 condition> and
    t1.<conditionField1> = <table1 condition 1> and
    t1.<conditionField2> = <table1 condition 2>

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[JustWondering]

That's exactly what I am loking for. Thank you so much.

 

[JustWondering]

And how do I do a query that will return common records from 3 tables which have 3 fileds match. I've tried the inner join inside another inner join but it gave me error.

Thanks

 

[billchris]

That would be something like this:

SELECT
    t1.id,
    <other t1 fields>,
    <t2 fields>,
    <t3 fields>
  FROM
    table1 t1
    inner join table2 t2 ON t1.id = t2.id
    inner join table3 t3 ON t1.id = t3.id
  WHERE
    t2.<conditionField> = <table2 condition> and
    t1.<conditionField1> = <table1 condition 1> and
    t1.<conditionField2> = <table1 condition 2> and
    t3.<conditionField1> = <table3 condition>

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[JustWondering]

Thanks again BillChris